Generating Random Number with Probability

Hi ixander and welcome to the forum,

I think you are looking for the Choose() function with a bunch of RandBetween() within the choices. Something like:

=CHOOSE(RANDBETWEEN(1,6),RANDBETWEEN(1,5),RANDBETWEEN(6,15),RANDBETWEEN(16,35),RANDBETWEEN(36,65),RANDBETWEEN(66,85),RANDBETWEEN(86,100))

Hi,
I think it does. Pull the formula down a few hundred rows and see.

Originally Posted by ixander

i would like to generate random number from column D with effect on the probability from column A
Attachment 445043

See the "Correct Distrib" worksheet in the attached Excel file.

The formula that I use in A11:A10010 is (see the comment for A11):

=CHOOSE(MATCH(RAND(),$C$2:$C$7,1),RANDBETWEEN($D$3,$E$3),RANDBETWEEN($D$4,$E$4),
 RANDBETWEEN($D$5,$E$5),RANDBETWEEN($D$6,$E$6),RANDBETWEEN($D$7,$E$7),RANDBETWEEN($D$8,$E$8))

C2:C7 are the cumulative probabilities, starting with 0%. The values in D3:E3 through D8:E8 are the lower and upper values of each range of numbers.

The distribution of the values in A11:A10010 is consistent with the required probabilities in A3:A8, to wit:

	C	D	E
10	Bin	Freq	Bin Prob
11	5	496	4.96%
12	15	960	9.60%
13	35	2032	20.32%
14	65	2999	29.99%
15	85	1988	19.88%
16	100	1525	15.25%
17	SUM	10000

Originally Posted by MarvinP

=CHOOSE(RANDBETWEEN(1,6),RANDBETWEEN(1,5),RANDBETWEEN(6,15),RANDBETWEEN(16,35),
  RANDBETWEEN(36,65),RANDBETWEEN(66,85),RANDBETWEEN(86,100))

That has the wrong distribution. The probability for each range of numbers is uniformly 1/6, about 16.67%. See the "MarvinP" worksheet in the attached Excel file.

The distribution of the values in A2:A10001 is (see the comment in A2):

	C	D	E
2	Bin	Freq	Bin Prob
3	5	1694	16.94%
4	15	1560	15.60%
5	35	1738	17.38%
6	65	1652	16.52%
7	85	1692	16.92%
8	100	1664	16.64%
9	SUM	10000
10	1/6	16.67%

Originally Posted by joeu2004

The formula that I use in A11:A10010 is (see the comment for A11):

=CHOOSE(MATCH(RAND(),$C$2:$C$7,1),RANDBETWEEN($D$3,$E$3),RANDBETWEEN($D$4,$E$4),
 RANDBETWEEN($D$5,$E$5),RANDBETWEEN($D$6,$E$6),RANDBETWEEN($D$7,$E$7),RANDBETWEEN($D$8,$E$8))

Much ado about nothing, perhaps. The distribution that ixander describes in the initial posting is simply one that comes from a uniform distribution between 1 and 100 inclusive.

For example, we expect the numbers 1 to 5 about 5% of the time, the numbers 6 to 15 about 10%, etc.

See the "Simple Distrib" worksheet in the attachment.

The formula in A2:A10001 is simply =RANDBETWEEN(1,100). (See the comment in A2.) It produced the following distribution:

	C	D	E
1	Bin	Freq	Bin Prob
2	5	490	4.90%
3	15	987	9.87%
4	35	2028	20.28%
5	65	3034	30.34%
6	85	1978	19.78%
7	100	1483	14.83%
8	SUM	10000

Ixander needs the more complicated CHOOSE(RAND,RANDBETWEEN,...) formula if the distribution in the initial posting is only an example, and ixander requires the flexibility for each group to have a different probability than it would with a uniform distribution.

Originally Posted by ixander

i would like to generate random number from column D with effect on the probability from column A

Originally Posted by joeu2004

Much ado about nothing, perhaps.

Indeed! I awoke this morning realizing that ixander probably misunderstood the assignment. Refer to the table below, which summarizes and embellishes ixander's JPEG and my attached Excel file.

Ixander probably needs to generate the demands in column B (B3:B8) according to the probability distribution in column A (A3:A8). Notice that the ultimate objective is to simulate demand starting in C11.

Someone might have told ixander to accomplish that by using RANDBETWEEN to generate integers in corresponding ranges (D1:D8, which are not needed for my purposes). That is a misdirection. But if the assignment requires that, I will explain how to change the paradigm below to that end.

The table below shows one way to simulate demand in rows 11 through 10011 (excerpted).

	A	B	C	D	E	F	G
1	Prob	Demand	Cuml Prob	Rnd Intvl
2			0.00%
3	5.00%	0	5.00%	1 to 5
4	10.00%	1	15.00%	6 to 15
5	20.00%	2	35.00%	16 to 35
6	30.00%	3	65.00%	36 to 65
7	20.00%	4	85.00%	66 to 85
8	15.00%	5	100.00%	86 to 100
9
10	Day	Rnd#	Sim Demand		Demand	Freq	Prob
11	1	0.9242114	5		0	479	4.79%
12	2	0.8095377	4		1	1021	10.21%
13	3	0.2860609	2		2	1958	19.58%
14	4	0.2308978	2		3	3078	30.78%
15	5	0.8072394	4		4	1969	19.69%
16	6	0.8546513	5		5	1495	14.95%
17	7	0.8199888	4		TOTAL	10000
18	8	0.0187098	0
19	9	0.3324532	2
	etc	etc	etc

C3:      =C2+A3
B11:     =RAND()
C11:     =LOOKUP(B11,$C$2:$C$7,$B$3:$B$8)
E11:     =B3
F11:F16: {=FREQUENCY(C11:C10010,E11:E16)}
F17:     =SUM(F11:F16)
G11:     =F11/$F$17
Copy C3  into C4:C8
Copy B11 into B12:B10010
Copy C11 into C12:C10010
Copy E11 into E12:E16
Copy G11 into G12:G16
Copy B11:B10010 and paste-value into B11:B10010 to freeze values

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

The frequency distribution table in E11:G16 demonstrates the probability distribution for the simulation, which is similar to A3:A8.

If the assignment requires that the random numbers starting in B11 be integers 1 through 100, make the following changes with appropriate changes to cell formats and column titles (ERRATA in red):

C3:   =C2+ROUND(A3*100,0)
C11:  =LOOKUP(B11-1,$C$2:$C$7,$B$3:$B$8)
B11:  =RANDBETWEEN(1,100) 

Hi Joeu,

Many of these questions are vague. I was thinking if he tried a Choose() function and then tested it using a frequency, s/he would discover if what I had given him was correct or not. It looks like you've gone to much deeper lengths than me. I wonder now what the real question was.

Hi FiberFlux,
I see you are pretty new to this forum. We like you to start your own thread instead of asking a new question at the bottom of someone else's question. Could you start your own new thread please?

In your post above you write:

My challenge is that I need the rows of random numbers to match the interval they are generated from.

I wish I knew what that meant. If you could give a few examples of what numbers are accepted and why, along with what answers are not allowed and why, it might help us understand the problem and get you the answer that you need.