I have some old borrowed code that I use to generate many thousands of random
numbers (investment returns) from a normal distribution, given a mean and
standard deviation. It works, except that the sequences aren't really random
and I often see the same numbers over and over. I know that the RAND()
function was updated to increase randomness, but I'm using VBA's RND
function. Does anyone know a way to increase the randomness of the returns
produced by this code?
Sub Getreturn
'where SD is std dev, meanarith is mean arithmetic return
Retn = gauss * SD + meanarith
End Sub
Function gauss()
Dim fac As Double, rt As Double, V1 As Double, V2 As Double
10 V1 = 2 * Rnd - 1
V2 = 2 * Rnd - 1
rt = V1 ^ 2 + V2 ^ 2
If (rt >= 1) Then GoTo 10
fac = Sqr(-2 * Log(rt) / rt)
gauss = V2 * fac
End Function
Bookmarks