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