random number based on probability in Excel

excel, excel-formula

Solution

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

Problem

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.

Original source