random number based on probability in Excel

Question

How can I create a random number generator which calculates a random number based on a probability?

For example, I have the following numbers with the probability they will occur starting in cell A1 and B1:

100 5%
75  10%
50  42%
30  30%
15  5%
0   8%

Thus, the formula would "randomly" return the number "15" 5% of all times.

Answer 1

Slightly less overhead:

Make a reference chart of your values, and a running total of probability:

C   D   E
100 5%  0
75  10% 5%
50  42% 15%
30  30% 57%
15  5%  87%
0   8%  92%

Then lookup a 0-1 random number on this chart. =LOOKUP(RAND(),$E$2:$E$7,$C$2:$C$7)

I generated 5224 numbers and produced this pivot chart of the results. Refreshing caused the percentages to waver a bit around the targets, but all attempts looked good.

Row Labels  Count   Percentage  Target
       0     421        8.06%      8%
      15     262        5.02%      5%
      30    1608       30.78%     30%
      50    2160       41.35%     42%
      75     490        9.38%     10%
     100     283        5.42%      5%
Grand Total 5224    100.00% 

Or you could do it with two cells and a long if statement: =RAND() =IF(A9<0.05,100,IF(A9<0.15,75, ... 0))...

Answer 2

This functionality comes as part of the analysis toolkit add in. You can find this in Excel options -> add ins -> manage add ins.

You want random number generation, and then pick the 'discrete' distribution. The input into this is the table you provided in your post.

"Discrete Characterized by a value and the associated probability range. The range must contain two columns: The left column contains values, and the right column contains probabilities associated with the value in that row. The sum of the probabilities must be 1."

jsarma's suggestion is also a good one....