Random number generation

[Stratuser]

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

 

[Joe Adams]

You might try the following Addin.
It is free, and it is excellent.

http://sunsite.univie.ac.at/Sp­readsite/poptools/

Joe Adams

 

[Tom Ogilvy]

what do your random numbers look like.

Do they contain 15 digits?

Let's say you have them displayed with only two decimal places - it would be
highly likely that you would see duplicate numbers. The concept of unique
and Normal are not consistent if your numbers represent an area.

for example Normal with a mean of 100 and with a SD of 3, 1.1 would
be an area.

 

[OnceTall]

I have tested the cycle time of the Rnd function in VBA, and it is
something like 16,700,000. After that many numbers, the entire cycle
repeats itself.

That may seem like a lot of numbers, but I often have need for more
than that. Does anyone have an add-in that generates more random
numbers and is pretty quick?

Thanks.

 

[Tom Ogilvy]

Did you look at the poptools addin suggest by Joe. It says its random
number generator is slower, but that may be a relative statement and of not
much consequence - I don't know, because I haven't used it.

http://sunsite.univie.ac.at/Spreadsite/poptools/

 

[Jerry W. Lewis]

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/[email protected]

implements the 2003 algorithm in VBA.


An even better algorithm is the Mersenne Twister

http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html
http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/FORTRAN/fortran.html
http://www-personal.engin.umich.edu/~wagnerr/MersenneTwister.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=3BB620CB.40404@no_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

 

[OnceTall]

Thank you Jerry and Tom.

Tom, I took a quick look at the poptools addin site. It says it is
free for non-commercial use. It's not impossible that I would sell my
sheet, so I don't want to go that route.

Jerry, I am still working my way through your links! I had looked at
the Mersenne Twister page before, but the implementations have so many
lines of code that I fear it will slow down my sims to a crawl. I
downloaded the NtRand freeware some time ago, but it looked to me that
it pops a bunch of random numbers into a table in Excel (30,000 at a
time?), and then I would have to cycle through the table and then
refresh it with another 30,000. That's not out of the question, but I
thought going back and forth into Excel from VBA would be slow. I was
hoping for something that I could use in VBA similar to the way RND is
used.

The link to Ian Smith's simple implementation in VBA of the "2003
algorithm" looks promising. It looks simple enough to be fast. Why
is it inferior to the Mersenne Twister? Is there a problem with the
degree of randomness? If so, how bad is it? (I'm guessing that's a
hard thing to quantify.)

btw, I'm only looking for a uniform distribution. (I don't need
normally distributed numbers).

Thanks again for the responses.

OnceTall

 

[Jerry W. Lewis]

The Excel 2003 algorithm was published in Applied Statistics in 1982.
At the time it was state of the art, passing all standard tests for
randomness and providing a far longer period than any other congruential
generator that could be implemented in small word size.

In the 1990's, tests for random number generators became much more
stringent. Mersene Twister provides a far longer period and better
joint independence among sets of random numbers.

Jerry