Random Numbers based Upon Probability

[IrishHawk01]

I'm spinning wheels. Here is what I'm attempting

I'm trying to use the empirical discrete probability distributions to generate 1000 days of Widget sales and returns.

I need to use Nested IF statements to generate the 1000 days of sales. So for 1000 days, generate a number in a column that represents widget sales for that day based upon the given probabilities.

[TABLE="width: 265"]
<tbody>[TR]
[TD]Number of Widgets Sold[/TD]
[TD]Probability[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.27[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.06[/TD]
[/TR]
</tbody>[/TABLE]

 

[shawnhet]

Two step process here is probably easiest.

Post a version of your table such as the following:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]0[/TD]
[TD="class: xl63, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]0.1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]0.31[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]0.44[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]0.67[/TD]
[TD="class: xl63, width: 64, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, align: right"]0.94[/TD]
[TD="class: xl63, width: 64, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
And then where you want the random numbers generated post this formula and copy down:

=VLOOKUP(RAND(),$A$1:$B$6,2,TRUE)

This will give the number of sales in column of your range above that is greater than or equal to the value in A and less than the next highest number in A.

Cheers,

 

[IrishHawk01]

Shawn,

Thanks for the response. However, I was able to utilize that solution before and am comfortable with your proposed solution. Unfortunately, the requirement is that I use nested IF statements.

Here is what I have for the statement:

=IF(I2<$E$2,0,IF(I2<$E$3,1,IF(I2<$E$4,2,IF(I2<$E$5,3,IF(I2<$E$6,4,IF(I2<$E$7,5,))))))

I2 is the random number, and E is the probability listed below for each of the associated number of widgets. My logic is close but I've been staring at it too long and can't find my mistake. Any help would be greatly appreciated.

[TABLE="width: 265"]
<tbody>[TR]
[TD]Number of Widgets Sold[/TD]
[TD]Probability[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.21[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.27[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.06[/TD]
[/TR]
</tbody>[/TABLE]

 

[shawnhet]

Well, I really shouldn't help you with what seems like homework

but try

=IF(I2<$E$2,0,IF(I2<$E$3+$E$2,1,IF(I2<$E$4+$E$3+$E$2 and so on...

Cheers,