The algorithm used by the worksheet RAND() depends on your Excel version

http://support.microsoft.com/kb/q86523/
http://support.microsoft.com/kb/q828795/

It also appears that RNGs in the worksheet, Analysis ToolPak, and VBA
each uses a different algorithm. ATP, VBA and pre 2003 worksheet
algorithms are not suitable for serious simmulation work.

http://groups.google.com/groups?selm...g .google.com

implements the 2003 algorithm in VBA.


An even better algorithm is the Mersenne Twister

http://www.math.sci.hiroshima-u.ac.j...at/MT/emt.html
http://www.math.sci.hiroshima-u.ac.j...N/fortran.html
http://www-personal.engin.umich.edu/...neTwister.html

which is implemented in the freeware NtRand

http://www.numtech.com/NtRand/


The simplest theoretically correct way to convert uniform random numbers
to normal random numbers is

=NORMSINV(RAND())

or

Application.NormSInv(RandNum)

but I would not recommend this unless you have Excel 2003 which has a
greatly improved NORMSINV() function. Alternately you could use the
PNormInv() VBA function that I posted in 2001

http://groups.google.com/groups?selm...0no_e-mail.com

or Ian Smith's inv_normal() VBA function

http://members.aol.com/iandjmsmith/examples.xls

that is part of an excellent library of probability functions.


Another theoretically correct approach is to use the Box-Muller
transformation to get 2*n Normal random numbers from 2*n Uniform random
numbers. This is a mathematically exact method that again may magnify
the problems of a poor pseudo-random number generator, but at least the
functions are implemented to machine accuracy in Excel. My reference
books are at the office, so I cannot vouch for these formulas that I got
from a Google search, but you take 2 Uniform random numbers (U1, U2) and
transform them to 2 Normal random numbers (N1, N2) with formulas like

N1 = SQRT(-2*LN(U1)) * COS(2*PI()*U2)
N2 = SQRT(-2*LN(U1)) * SIN(2*PI()*U2)

Jerry

Stratuser wrote:

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