Help with math problem

[JenniferMurphy]

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

 

[johnnyL]

Do you have any data to offer so we can check our results?

 

[Eric W]

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
	A	B	C	D	E	F	G	H
1					0
2	p	0.7		0.7	0.7		A	A
3	N	5		0.211712	0.911712		B
4	r	0.302445		0.064031	0.975743		C
5	sum of terms	1.000966		0.019366	0.995109		D
6				0.005857	1.000966		E
Sheet9

Cell Formulas
Range	Formula
D2:D6	D2	=B2*B4^SEQUENCE(B3,,0)
H2	H2	=INDEX(G2:G6,MATCH(RAND(),E1:E5))
B5	B5	=B2*SERIESSUM(B4,0,1,SEQUENCE(B3,,,0))
E2:E6	E2	=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.

 

[JenniferMurphy]

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...

 

[JenniferMurphy]

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.

Jigsaw puzzles.xlsx
	C	D	E	F
4	10	Number of items
5
6	X	P1	P2	P3
7	1	0.1818	0.1636	0.2000
8	2	0.1636	0.1455	0.1778
9	3	0.1455	0.1273	0.1556
10	4	0.1273	0.1091	0.1333
11	5	0.1091	0.0909	0.1111
12	6	0.0909	0.0727	0.0889
13	7	0.0727	0.0545	0.0667
14	8	0.0545	0.0364	0.0444
15	9	0.0364	0.0182	0.0222
16	10	0.0182	0.0000	0.0000
17	55	1.0000	0.8182	1.0000
Sheet2

Cell Formulas
Range	Formula
C4	C4	=COUNT(Table3[X])
C7:C16	C7	=ROW()-ROW(Table3[[#Headers],[X]])
D7:D16	D7	=(NumItems-[@X]+1)/Table3[[#Totals],[X]]
E7:E16	E7	=(NumItems-[@X])/Table3[[#Totals],[X]]
F7:F16	F7	=(NumItems-[@X])/(Table3[[#Totals],[X]]-NumItems)
C17	C17	=SUBTOTAL(109,[X])
D17	D17	=SUBTOTAL(109,[P1])
E17	E17	=SUBTOTAL(109,[P2])
F17	F17	=SUBTOTAL(109,[P3])

Named Ranges
Name	Refers To	Cells
NumItems	=Sheet2!$C$4	D7:F16

Here it is with 25 items.

Jigsaw puzzles.xlsx
	C	D	E	F
4	25	Number of items
5
6	X	P1	P2	P3
7	1	0.0769	0.0738	0.0800
8	2	0.0738	0.0708	0.0767
9	3	0.0708	0.0677	0.0733
10	4	0.0677	0.0646	0.0700
11	5	0.0646	0.0615	0.0667
12	6	0.0615	0.0585	0.0633
13	7	0.0585	0.0554	0.0600
14	8	0.0554	0.0523	0.0567
15	9	0.0523	0.0492	0.0533
16	10	0.0492	0.0462	0.0500
17	11	0.0462	0.0431	0.0467
18	12	0.0431	0.0400	0.0433
19	13	0.0400	0.0369	0.0400
20	14	0.0369	0.0338	0.0367
21	15	0.0338	0.0308	0.0333
22	16	0.0308	0.0277	0.0300
23	17	0.0277	0.0246	0.0267
24	18	0.0246	0.0215	0.0233
25	19	0.0215	0.0185	0.0200
26	20	0.0185	0.0154	0.0167
27	21	0.0154	0.0123	0.0133
28	22	0.0123	0.0092	0.0100
29	23	0.0092	0.0062	0.0067
30	24	0.0062	0.0031	0.0033
31	25	0.0031	0.0000	0.0000
32	325	1.0000	0.9231	1.0000
Sheet3

Cell Formulas
Range	Formula
C4	C4	=COUNT(Table4[X])
C7:C31	C7	=ROW()-ROW(Table4[[#Headers],[X]])
D7:D31	D7	=(NumItems-[@X]+1)/Table4[[#Totals],[X]]
E7:E31	E7	=(NumItems-[@X])/Table4[[#Totals],[X]]
F7:F31	F7	=(NumItems-[@X])/(Table4[[#Totals],[X]]-NumItems)
C32	C32	=SUBTOTAL(109,[X])
D32	D32	=SUBTOTAL(109,[P1])
E32	E32	=SUBTOTAL(109,[P2])
F32	F32	=SUBTOTAL(109,[P3])

Named Ranges
Name	Refers To	Cells
NumItems	=Sheet3!$C$4	D7:F31

I'm going to bed. I'll check any replies I will check any replies in the morning.

 

[JenniferMurphy]

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.

 

[Eric W]

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
	H	I
1
2		1
3
4	10	Number of items
5	0.2	Starting probability
6	X	P1
7	1	0.2
8	2	0.177777778
9	3	0.155555556
10	4	0.133333333
11	5	0.111111111
12	6	0.088888889
13	7	0.066666667
14	8	0.044444444
15	9	0.022222222
16	10	0
Sheet10

Cell Formulas
Range	Formula
I2	I2	=SUM(I7#)
H7:H16	H7	=SEQUENCE(H4)
I7:I16	I7	=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.

 

[JenniferMurphy]

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