Normal Distrubtions with Upper/Lower Bounds
 

I want to generate random numbers in VBA. I also want to dictate the
upper and lower bounds of the random number. I know how to do this
using the Rnd Function. As follows:

RandomNum = Int((UpperBounds - LowerBounds) + 1)* Rnd +
LowerBounds

However, given the short comings of the RND function I have come
across Myrna Larsons code for a normal distribtion (See below). How
do you incorporate the upper/lower bound logic from above into Myrna's
code? Thanks in advance.

Function RandomNormal(Optional Mean As Double = 0, _
Optional SD As Double = 1) As Double
'This function is to enable to use normal distrubtion vs the Rnd
function which is uniform
'This function can be called if a uniform distrubution is not
warranted
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


Terry

Normal Distrubtions with Upper/Lower Bounds
 

However, given the short comings of the RND function

Myrna's function is using the RND function. Also, if you want uniformly
distributed random numbers, then you wouldn't use the normal distribution to
get them.

--
Regards,
Tom Ogilvy

wrote in message
om...
I want to generate random numbers in VBA. I also want to dictate the
upper and lower bounds of the random number. I know how to do this
using the Rnd Function. As follows:

RandomNum = Int((UpperBounds - LowerBounds) + 1)* Rnd +
LowerBounds

However, given the short comings of the RND function I have come
across Myrna Larsons code for a normal distribtion (See below). How
do you incorporate the upper/lower bound logic from above into Myrna's
code? Thanks in advance.

Function RandomNormal(Optional Mean As Double = 0, _
Optional SD As Double = 1) As Double
'This function is to enable to use normal distrubtion vs the Rnd
function which is uniform
'This function can be called if a uniform distrubution is not
warranted
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


Terry