Help with math problem

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,676
Office Version
  1. 365
Platform
  1. Windows
I fear my math days are dwindling even faster than I thought. I hope someone can help me with this.

I need a formula that will select an item from a list of N items such that:
  • The odds of selecting item 1 is p (0<p<1)
  • The odds of selecting item N is zero
  • The odds of selecting items 1 thru N decrease exponentially (or geometrically) from p to zero.
Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Strictly speaking, what you want is impossible. If the probabilities decrease in a geometric or exponential way, that means multiplying each successive term by a ratio (r) that is non-zero. So the last term will never be exactly zero, but rounding errors might work in your favor.

Also, when I did the math, what you're essentially asking for is taking the formula for the sum of a geometric series Sn=a(1-r^n)/(1-r), setting a = p (your first value), and setting Sn to 1 (since the sum of probabilities must add up to 1), then solving for r. r would be the factor you need to multiply p by to get the probability of the next item. You can't solve this equation for r in a closed formula, which is probably why you had some difficulties.

All is not lost though. Consider:

Book1
ABCDEFGH
10
2p0.70.70.7AA
3N50.2117120.911712B
4r0.3024450.0640310.975743C
5sum of terms1.0009660.0193660.995109D
60.0058571.000966E
Sheet9
Cell Formulas
RangeFormula
D2:D6D2=B2*B4^SEQUENCE(B3,,0)
H2H2=INDEX(G2:G6,MATCH(RAND(),E1:E5))
B5B5=B2*SERIESSUM(B4,0,1,SEQUENCE(B3,,,0))
E2:E6E2=SUM(D$2:D2)
Dynamic array formulas.


Set B2 to p, B3 to N. Then use Goal Seek to set B5 to 1 by changing B4. Once you have r, you can get the probabilities of all the items with the D2 formula. Then the E2 formula gets you the aggregate sums of the probabilities. Finally, in H2 you can pick a random number from 0 to 1 with RAND(), and use that as a search value in the E1:E5 table, and it will give you a number from 1 to 5 in the probabilities from column D.

Some issues. First, I'm sure you'd rather not have to use Goal Seek. We know the ratio will be <1 (or the probabilities will get bigger), so we can create an array formula that check all 2-digit values from .01 to .99 and pick the closest. If you need more accuracy, we can do .001 to .999, but that'll take 10 times longer.

Next, you probably want it in a single formula. Possible, if it's a LET and it's complicated enough.

Before I work on combining all the pieces, I wanted to see if this is in fact what you're looking for. Let me know.
 
Last edited:
Upvote 0
Strictly speaking, what you want is impossible. If the probabilities decrease in a geometric or exponential way, that means multiplying each successive term by a ratio (r) that is non-zero. So the last term will never be exactly zero, but rounding errors might work in your favor.

That's what I was afraid of...

After reading your reply, I realized that I had not taken into account that the probabilities have to sum to 1. That lead me to calculate the probabilities as their weight compared to the sum of the weights. I was about to post a minisheet, but realized that I have not installed xl2bb. I'll do that the post again...
 
Upvote 0
I got an error trying to install xl2bb on my new Win 11 conputer, so I'm doing it from my old Win 10 machine.

Here's a short version with only 10 items. The X column is the item numbers.

In P1, I just divided the complement of the item number by the sum of the item numbers (here 55). This is nice because the probabilities sum to 1.

In P2, I removed the "+1" to make the probability of the last item zero, but now they don't sum to 1.

I fixed that in P3.

Cell Formulas
RangeFormula
C4C4=COUNT(Table3[X])
C7:C16C7=ROW()-ROW(Table3[[#Headers],[X]])
D7:D16D7=(NumItems-[@X]+1)/Table3[[#Totals],[X]]
E7:E16E7=(NumItems-[@X])/Table3[[#Totals],[X]]
F7:F16F7=(NumItems-[@X])/(Table3[[#Totals],[X]]-NumItems)
C17C17=SUBTOTAL(109,[X])
D17D17=SUBTOTAL(109,[P1])
E17E17=SUBTOTAL(109,[P2])
F17F17=SUBTOTAL(109,[P3])
Named Ranges
NameRefers ToCells
NumItems=Sheet2!$C$4D7:F16


Here it is with 25 items.

Cell Formulas
RangeFormula
C4C4=COUNT(Table4[X])
C7:C31C7=ROW()-ROW(Table4[[#Headers],[X]])
D7:D31D7=(NumItems-[@X]+1)/Table4[[#Totals],[X]]
E7:E31E7=(NumItems-[@X])/Table4[[#Totals],[X]]
F7:F31F7=(NumItems-[@X])/(Table4[[#Totals],[X]]-NumItems)
C32C32=SUBTOTAL(109,[X])
D32D32=SUBTOTAL(109,[P1])
E32E32=SUBTOTAL(109,[P2])
F32F32=SUBTOTAL(109,[P3])
Named Ranges
NameRefers ToCells
NumItems=Sheet3!$C$4D7:F31


I'm going to bed. I'll check any replies I will check any replies in the morning.
 
Upvote 0
Tomorrow I'll look into skewing (adjusting) the probabilities so that the 1st item has a fixed probability, like 20%, and the rest decline to zero, but still sum to 1.
 
Upvote 0
I'm not sure if you're looking for an answer for anything, but I played around a little. I already figured out how to do the geometric series in post 3. If you're happy with an arithmetic series, like your samples, then it's a bit easier.

Book1
HI
1
21
3
410Number of items
50.2Starting probability
6XP1
710.2
820.177777778
930.155555556
1040.133333333
1150.111111111
1260.088888889
1370.066666667
1480.044444444
1590.022222222
16100
Sheet10
Cell Formulas
RangeFormula
I2I2=SUM(I7#)
H7:H16H7=SEQUENCE(H4)
I7:I16I7=LET(n,H4,s,H5,d,(2*n*s-2)/(n-1)/n,SEQUENCE(n,,s,-d))
Dynamic array formulas.


In this case, it's easy to solve for d, which is the difference between each term. One thing to note though, is that the starting probability must be between 1/n and 2/n. If it's less than 1/n, then the values will increase, if it's over 2/n, then you'll get negative values. Exactly 2/n will make the last value 0, which you want. Otherwise, just do the calculation for n-1 and set the last value to 0.
 
Upvote 0
I'm not sure if you're looking for an answer for anything, but I played around a little. I already figured out how to do the geometric series in post 3. If you're happy with an arithmetic series, like your samples, then it's a bit easier.
I'll play with your solution when I get a minute. I also found a solution using a quadratic.

I'm having a little trouble getting Excel and xl2bb working on this new laptop. I'll post when I do.

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top