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%

So, based on above, numbers from 21 to 60 have the highest probability of occurring in a large trial run. What I'd want to do is have a column with 10,000 cells going down, each spitting out any of the numbers above based on probability of occurring and 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?

Thanks!

Hi,

Am Thu, 28 May 2015 06:57:25 -0700 (PDT) schrieb Haas:

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%

So, based on above, numbers from 21 to 60 have the highest probability of occurring in a large trial run. What I'd want to do is have a column with 10,000 cells going down, each spitting out any of the numbers above based on probability of occurring and 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?

try it with VBA:

Sub Rand()
Dim i As Long

For i = 1 To 500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*10,0)+1")
Next
For i = 501 To 1500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*20,0)+11")
Next
For i = 1501 To 8000
Cells(i, 1) = Evaluate("=TRUNC(RAND()*60,0)+21")
Next
For i = 8001 To 9500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*85,0)+61")
Next
For i = 9501 To 10000
Cells(i, 1) = Evaluate("=TRUNC(RAND()*100,0)+86")
Next

MsgBox Application.Average(Range("A1:A10000"))
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Thursday, May 28, 2015 at 1:49:10 PM UTC-4, Claus Busch wrote:
Hi,

Am Thu, 28 May 2015 06:57:25 -0700 (PDT) schrieb Haas:

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%

So, based on above, numbers from 21 to 60 have the highest probability of occurring in a large trial run. What I'd want to do is have a column with 10,000 cells going down, each spitting out any of the numbers above based on probability of occurring and 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?

try it with VBA:

Sub Rand()
Dim i As Long

For i = 1 To 500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*10,0)+1")
Next
For i = 501 To 1500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*20,0)+11")
Next
For i = 1501 To 8000
Cells(i, 1) = Evaluate("=TRUNC(RAND()*60,0)+21")
Next
For i = 8001 To 9500
Cells(i, 1) = Evaluate("=TRUNC(RAND()*85,0)+61")
Next
For i = 9501 To 10000
Cells(i, 1) = Evaluate("=TRUNC(RAND()*100,0)+86")
Next

MsgBox Application.Average(Range("A1:A10000"))
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks for a good suggestion but this method pretty much gives you the same answer/number +/- 1 every time.

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%

Maybe something like this would work.
*
I put a "helper" column in E1:E20 containing
=RANDBETWEEN(1,10)
=RANDBETWEEN(11,20)
=RANDBETWEEN(11,20)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(61,85)
=RANDBETWEEN(61,85)
=RANDBETWEEN(61,85)
=RANDBETWEEN(86,100)
Notice the repetitions inspired by the "probability of occurring" distribution in the original post. (The column can be hidden to reduce clutter.)
*
Then in A1, I put
=INDEX($E$1:$E$20,INT(20*RAND())+1)
and copied down to make the desired list.

"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%.

PS....
"joeu2004" wrote:
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))

Despite appearances, only two random functions are calculated for each cell.
CHOOSE only evaluates the parameter selected by MATCH.

On Thursday, May 28, 2015 at 9:57:27 AM UTC-4, 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%

So, based on above, numbers from 21 to 60 have the highest probability of occurring in a large trial run. What I'd want to do is have a column with 10,000 cells going down, each spitting out any of the numbers above based on probability of occurring and 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?

Thanks!

Thank you all - the solutions were all helpful and I've learned something from each of you so thanks!