formula for pyramid distribution? For Google Sheets

[smking204]

I've got a doozy for y'all. I want to create a formula that will return a random integer according to a pyramid distribution.

For example, A1 might serve as my input, which is itself a randomized integer, let's call it 24 for now. In B1, then, I want my pyramid formula, which will return a new integer that is + or - 50% of the input, but weighted toward the middle (12 and 36 would each have one chance of appearing, 13 and 35 would each have two chances, 14 and 34 would each have 3, and so on).

The formula needs to be 1) dependent on the variable input, and 2) able to be retooled for a different percentage

WAY too much math for me, but I hope to get some responses to dissect and get a little better at this sort of thing. Thanks a ton!

 

[Rick Rothstein]

I am a little unclear as to the final result that you want. Are you looking for a single formula that returns a single value picked according to your stated requirement? Of are you looking for something else? If so, what?

 

[smking204]

I am a little unclear as to the final result that you want. Are you looking for a single formula that returns a single value picked according to your stated requirement? Of are you looking for something else? If so, what?

thanks, yes. I'm looking for a single integer.

 

[smking204]

thanks, yes. I'm looking for a single integer.

sorry, yes, single formula that returns a single integer

 

[Eric W]

I spent wayyyy too much time playing around with this, but I came up with a function:

Book1 (version 1).xlsb
	A	B	C	D	E
1	0	Midpoint	Range		Random #
2	0	24	50%		21
3	0
4	0				6
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	17	.................
13	29	.............................
14	33	.................................
15	38	......................................
16	73	.........................................................................
17	60	............................................................
18	72	........................................................................
19	99	...................................................................................................
20	111	...............................................................................................................
21	117	.....................................................................................................................
22	143	...............................................................................................................................................
23	151	.......................................................................................................................................................
24	155	...........................................................................................................................................................
25	157	.............................................................................................................................................................
26	133	.....................................................................................................................................
27	114	..................................................................................................................
28	110	..............................................................................................................
29	80	................................................................................
30	90	..........................................................................................
31	71	.......................................................................
32	50	..................................................
33	35	...................................
34	25	.........................
35	25	.........................
36	12	............
37	0
38	0
39	0
40	0
End

Cell Formulas
Range	Formula
E2	E2	=LET(r,INT(B2*C2)+1,s,SEQUENCE(,r*2-1,0),si,s*(s+1)/2,sd,IFERROR(INDEX(si,EXP(LN(s-r+1)))*2,0),fs,si-sd+1,v,MATCH(RANDBETWEEN(1,MAX(fs)),fs),B2-r+v)
E4	E4	=INT(NORM.INV(RAND(),B2,B2*C2/2))
B12:B40	B12	=REPT(".",A12)

The E2 formula does what you want. It delivers a random number within the midpoint and range selected, weighted by your pyramid design. I ran a macro that calculated it 2000 times and plotted the results in rows 12 to 36, the row corresponding to the result numbers. Then a simple plot line to show how often each number was selected. As you can see, the result is very close to being a triangle shape.

However, if you're a bit more flexible and you can use a normal distribution, Excel has a built-in formula that does that. See the E4 formula. When I plotted 2000 results the same as I did for the E2 formula, the resulting plot was nearly indistinguishable (other than a few numbers trailing off the ends).

Hope you find this useful!

 

[smking204]

I spent wayyyy too much time playing around with this, but I came up with a function:

Book1 (version 1).xlsb
	A	B	C	D	E
1	0	Midpoint	Range		Random #
2	0	24	50%		21
3	0
4	0				6
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	17	.................
13	29	.............................
14	33	.................................
15	38	......................................
16	73	.........................................................................
17	60	............................................................
18	72	........................................................................
19	99	...................................................................................................
20	111	...............................................................................................................
21	117	.....................................................................................................................
22	143	...............................................................................................................................................
23	151	.......................................................................................................................................................
24	155	...........................................................................................................................................................
25	157	.............................................................................................................................................................
26	133	.....................................................................................................................................
27	114	..................................................................................................................
28	110	..............................................................................................................
29	80	................................................................................
30	90	..........................................................................................
31	71	.......................................................................
32	50	..................................................
33	35	...................................
34	25	.........................
35	25	.........................
36	12	............
37	0
38	0
39	0
40	0
End

Cell Formulas
Range	Formula
E2	E2	=LET(r,INT(B2*C2)+1,s,SEQUENCE(,r*2-1,0),si,s*(s+1)/2,sd,IFERROR(INDEX(si,EXP(LN(s-r+1)))*2,0),fs,si-sd+1,v,MATCH(RANDBETWEEN(1,MAX(fs)),fs),B2-r+v)
E4	E4	=INT(NORM.INV(RAND(),B2,B2*C2/2))
B12:B40	B12	=REPT(".",A12)

The E2 formula does what you want. It delivers a random number within the midpoint and range selected, weighted by your pyramid design. I ran a macro that calculated it 2000 times and plotted the results in rows 12 to 36, the row corresponding to the result numbers. Then a simple plot line to show how often each number was selected. As you can see, the result is very close to being a triangle shape.

However, if you're a bit more flexible and you can use a normal distribution, Excel has a built-in formula that does that. See the E4 formula. When I plotted 2000 results the same as I did for the E2 formula, the resulting plot was nearly indistinguishable (other than a few numbers trailing off the ends).

Hope you find this useful!

Oh, wow, thanks! Plenty for me to dig into here. I should also clarify that I need it to work in Google Sheets. Apparently =LET doesn't exist there yet, which is a shame. I'll try to note this in my original post. I'll see what work-arounds I can find in the meantime.

 

[Eric W]

We have a special forum called "General Discussion and Other Applications" where questions about other spreadsheet programs should be placed. And definitely mention the name of the program you're using. It would have saved me a lot of time. I really don't know much about Google Sheets, so I can't really tell you how to adapt the formula for GS. In theory, any LET function can be rewritten without LET, but it would be probably 5 or 6 times longer. And I'm not going to do that for fear that I'll get caught in another difference between the 2 programs.

My best advice would probably be to use the NORM.INV function (if it exists in GS), and add a section to see if the returned value exceeds the end points (which would raise the probability of hitting an end point, but maybe not by enough to worry about).

 

[Fluff]

@smking204 as Eric has mentioned, in future when asking questions about spreadsheets other than Excel please post in the General Discussion & Other Applications section of the board & make it clear in you title what app you are using.
I have done that for you this time.

 

[smking204]

I spent wayyyy too much time playing around with this, but I came up with a function:

Book1 (version 1).xlsb
	A	B	C	D	E
1	0	Midpoint	Range		Random #
2	0	24	50%		21
3	0
4	0				6
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	17	.................
13	29	.............................
14	33	.................................
15	38	......................................
16	73	.........................................................................
17	60	............................................................
18	72	........................................................................
19	99	...................................................................................................
20	111	...............................................................................................................
21	117	.....................................................................................................................
22	143	...............................................................................................................................................
23	151	.......................................................................................................................................................
24	155	...........................................................................................................................................................
25	157	.............................................................................................................................................................
26	133	.....................................................................................................................................
27	114	..................................................................................................................
28	110	..............................................................................................................
29	80	................................................................................
30	90	..........................................................................................
31	71	.......................................................................
32	50	..................................................
33	35	...................................
34	25	.........................
35	25	.........................
36	12	............
37	0
38	0
39	0
40	0
End

Cell Formulas
Range	Formula
E2	E2	=LET(r,INT(B2*C2)+1,s,SEQUENCE(,r*2-1,0),si,s*(s+1)/2,sd,IFERROR(INDEX(si,EXP(LN(s-r+1)))*2,0),fs,si-sd+1,v,MATCH(RANDBETWEEN(1,MAX(fs)),fs),B2-r+v)
E4	E4	=INT(NORM.INV(RAND(),B2,B2*C2/2))
B12:B40	B12	=REPT(".",A12)

The E2 formula does what you want. It delivers a random number within the midpoint and range selected, weighted by your pyramid design. I ran a macro that calculated it 2000 times and plotted the results in rows 12 to 36, the row corresponding to the result numbers. Then a simple plot line to show how often each number was selected. As you can see, the result is very close to being a triangle shape.

However, if you're a bit more flexible and you can use a normal distribution, Excel has a built-in formula that does that. See the E4 formula. When I plotted 2000 results the same as I did for the E2 formula, the resulting plot was nearly indistinguishable (other than a few numbers trailing off the ends).

Hope you find this useful!

also, I'm looking into your E4 formula and that "normal distribution" concept. Seems like "Normal Distribution" is a perfectly good way to go with this, but I do need to limit the results to +/- 50%

EDIT: I just saw the above two replies, sorry for the confusion and thanks for the help!

 

[Eric W]

I tweaked the normal distribution formula so that it limits the results to +/- 50%. It works, but the endpoints will be a bit higher than expected (see the graph below). I experimented with changing the size of the standard deviation. I originally used a divisor of 2 which made the endpoints significantly higher. I used a divisor of 3, which made the endpoints about what you'd want, but it made the rest of the values drop off much quicker too. I think a divisor of 2.5 is about the best compromise.

Book1 (version 1).xlsb
	A	B	C	D	E
1	0	Midpoint	Range		Random #
2	0	24	50%		25
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	25	.........................
13	11	...........
14	29	.............................
15	39	.......................................
16	50	..................................................
17	56	........................................................
18	89	.........................................................................................
19	111	...............................................................................................................
20	115	...................................................................................................................
21	140	............................................................................................................................................
22	154	..........................................................................................................................................................
23	200	........................................................................................................................................................................................................
24	168	........................................................................................................................................................................
25	153	.........................................................................................................................................................
26	132	....................................................................................................................................
27	116	....................................................................................................................
28	109	.............................................................................................................
29	78	..............................................................................
30	73	.........................................................................
31	39	.......................................
32	40	........................................
33	25	.........................
34	24	........................
35	9	.........
36	15	...............
37	0
End

Cell Formulas
Range	Formula
E2	E2	=MEDIAN(INT(NORM.INV(RAND(),B2,B2*C2/2.5)),INT(B2-B2*C2),ROUNDUP(B2+B2*C2,0))
B12:B37	B12	=REPT(".",A12)

As far as I know, all these functions exist in Google Sheets, although with slightly different names (NORM.INV --> NORMINV). I hope it works for you, but if not, you'll have to find someone else to step in. Good luck!