Hi,
Damon was referring to the Random Number Generator in the Analysis ToolPak in one part of his discussion. It will allow you to choose many different distributions.
For your purposes it would be OK, I believe, but I have read that there are problems with the RNG, although that is for serious statistical work.
With the ATP loaded, Tools>Data Analysis>Random Number Generator and choose from a bunch of options.
Another option would be to use the NORMINV function with a random probability...
=NORMINV(Rand(),mean,standard deviation)
The recommendation from the experts (Mike Middleton, Jerry W. Lewis and Dave Braden among others -- three stats gurus who are also Excel wizards) is that NORMINV has problems in versions prior to XP. That said, these guys need a degree of accuracy and randomness far beyond what you or I would likely need.
If you don't have XP and want a "better" function, the following UDF should work...<pre>Function RandomNormal(Optional Mean As Double = 0, _
Optional SD As Double = 1) As Double
'''''''''''''''''''''''''
' posted by Myrna Larson
' microsoft.public.excel.programming
' 24 Aug 1999
'returns a normally distributed random number
'distribution mean = Mean, sigma = SD
Static Initialized As Boolean
Static TwoPi As Double
Static Have2nd As Boolean
Static X1 As Double, X2 As Double
Application.Volatile
If Initialized = False Then
TwoPi = 8 * Atn(1)
Have2nd = False
Randomize Timer
Initialized = True
End If
If Have2nd = False Then
X1 = TwoPi * Rnd
X2 = Sqr(-2 * Log(Rnd))
RandomNormal = Cos(X1) * X2 * SD + Mean
Have2nd = True
Else
'return 2nd value from previous call
RandomNormal = Sin(X1) * X2 * SD + Mean
Have2nd = False
End If
End Function</pre>
Damon, if you have a UDF already coded, would you please post it? I, for one, would like to see it as it is probably very nice.
Finally, it took a while, but I found one post I was looking for...
http://groups.google.com/groups?hl=...d2cf6$b5e98940$37e0ca8a@Middleton_M.usfca.edu
That should help with a bounded Normal.
_________________
Bye,
Jay
EDIT: One other RandomNormal routine I found. The author is Dave Braden.<pre>Function RandomNormal(Optional Mean As Double = 0, _
Optional SD As Double = 1) As Double
Static HaveX1 As Boolean
Dim V1 As Double, V2 As Double, S As Double
Static X1 As Double
Dim X2 As Double
If HaveX1 = False Then
Do
V1 = Rnd() * 2 - 1
V2 = Rnd() * 2 - 1
S = V1 * V1 + V2 * V2
Loop While S >= 1# Or S = 0#
S = Sqr(-2 * Log(S) / S)
X1 = V1 * S
X2 = V2 * S
HaveX1 = True
RandomNormal = X2 * SD + Mean
Else
HaveX1 = False
RandomNormal = X1 * SD + Mean
End If
End Function 'RandomNormal</pre>
This message was edited by Jay Petrulis on 2002-09-16 17:31