"Haas" wrote:
I am trying to create a model in Excel to spit out a
number based on a computing probabilities associated
with outcomes. An example would be best:
Numbers Probability of occurring
1-10 5%
11-20 10%
21-60 65%
61-85 15%
86-100 5%
[....] What I'd want to do is have a column with 10,000
cells going down, [....] then take an average of the
10,000 cells. Is there a Random function which would
generate a number based on the likelihood of it occurring
as per above table?

Enter the following formula into A1:A10000:

=CHOOSE(MATCH(RAND(),{0,0.05,0.15,0.8,0.95}),RANDB ETWEEN(1,10),
RANDBETWEEN(11,20),RANDBETWEEN(21,60),RANDBETWEEN( 61,85),
RANDBETWEEN(86,100))

For the average, enter the following formula into B1:

=AVERAGE(A1:A10000)

Note the expected average is:

=SUMPRODUCT({5.5,15.5,40.5,73,93},{0.05,0.1,0.65,0 .15,0.05})

To confirm the distribution, enter the following:

C1: 10
C2: 20
C3: 60
C4: 85
C5: 100

Select D1:D5 and array-enter the following formula (press ctrl+shift+Enter,
not just Enter):

=FREQUENCY(A1:A10000,C1:C5)

Excel will display the formula with curly braces around it.

Finally, enter the following:

D6: =SUM(D1:D5)
E1: =D1/$D$6

formatted as Percentage. Copy E1 into E2:E5. The percentages should be
approximately 5%, 10%, 65%, 15% and 5%.