RAND function - Not truelly Random?!?

Posted by John Earnhart on November 29, 2000 3:03 PM

It's come to my attention that the RAND function in
Excel does not appear to be "flat". It appears to
severely violate the run property of a truely random
number generator. It can only generate a maximum of 7
simultaneous numbers in a row. It sounds freakish,
but extensive experimentation and attempts at work-arounds
(on my part) seem to support it.

FYI, this limitation means the RAND function can never
generate a number lower than 1E-8, even though the RAND
function produces a 15 digit number.

Anyone else familiar with this? If so, any ideas on how to
work around this?

Thanks,
John

Posted by Tim Francis-Wright on November 29, 2000 8:47 PM

According to the brain trust in Redmond,
http://support.microsoft.com/support/kb/articles/q86/5/23.asp

In Microsoft Excel, the RAND function uses the following iterative method to calculate pseudo-random
numbers:

The first random number:

random_number=fractional part of (9821 * r + 0.211327),
where r = .5
[In Excel 5.0 and up, the initial r is determined from
the system clock.]

Successive random numbers:
random_number=fractional part of (9821 * r + 0.211327),
where r = the previous random number

This formula will provide up to 1 million different numbers.

An article I found at www.mailbase.ac.uk/lists/assume/2000-09/0019.html
says that the cycle repeats after 941,955 numbers.
The same article points out that the Rnd() function
in VBA has a longer repeat period--16,777,216=2^24.

It seems that there are some statistical add-ins for Excel
that include pseudorandom number generators that are much better
than the VBA Rnd function. Some of these add-ins are
free; others are not. [Expect to see much gnashing
of teeth about Excel's poor statistical functions.]

Posted by John Earnhart on November 30, 2000 2:31 PM

Tim-Francis,

Thanks for your post. It was *very* enlightening and also usefull.

Thanks again,
John