Box Muller Method with VBA

[matthaus]

For my job, I'm supposed to generated 'n' normally distributed random numbers and calculate only the frequencies using the box muller method. I can't seem to figure it out or get a good internet search on it.

Here are some details about what I know and what I've found out so far:

-Each successful iteration of the method creates two numbers that are drawnfrom a standard normal distribution.

-The approach first creates two random numbers, rand1 and rand2 between 1and 1. If the sum of the squares of these numbers is within the unit circle, thenthe two normal deviates are defined by:

1. {x1, x2} = {rand1 * sqr(( -2 * ln(s1))/s1) , rand2 * sqr(( - 2 * ln(s1))/s1)}
   

where s1 = rand1 ^ 2 + rand2 ^ 2


I am supposed to generate 10,000,000 random numbers and output only the frequency of those random numbers.

For instance:

at -1.0 I might have a distribution of .00000002
at -0.9 I might have a distribution of .00000005
.....

at 0 I might have a distribution of .00003
....

at 0.9 I might have a distribution of .000000003

based on the random number generator. Note: the example I have provided, probably won't be correct due to the random numbers generated, but just to give you can idea of what you're looking for.


Here is the code that I have typed so far:

Sub NormStandardRandom3()
Randomize 'Initializes the VBA random number generator
Dim Distribution(-10 To 10) As Double
Application.ScreenUpdating = False
Range("E2") = Time

n = Range("B2").Value
Dim x1 As Double, x2 As Double, w As Double, z As Double, y1 As Double, y2 As Double

For index = 1 To n
x1 = Application.WorksheetFunction.Norm_S_Inv(Rnd())
x2 = Application.WorksheetFunction.Norm_S_Inv(Rnd())
w = x1 * x1 + x2 * x2
If w < -1 Then
Distribution(-10) = Distribution(-10) + 1
ElseIf w > 1 Then
Distribution(10) = Distribution(10) + 1
Else
z = Sqr((-2 * Log(w)) / w)
y1 = x1 * z
y2 = x2 * z
Distribution(Int(y2)) = Distribution(Int(y2)) + 1
Distribution(Int(y1)) = Distribution(Int(y1)) + 1
End If
Next index


For index = -10 To 10
Range("B7").Cells(index + 11, 1) = Distribution(index) / n
Next index


End Sub



I feel like i'm fairly close. Please any help would be greatly appreciated. Thank you you kind souls.