Microsoft KB Archive/828795

= Description of the RAND function in Excel 2007 and in Excel 2003 =

Article ID: 828795

Article Last Modified on 1/16/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003

-



SUMMARY
This article describes the modified algorithm that is used in the random number generator function, RAND, in Microsoft Office Excel 2007 and in Microsoft Office Excel 2003.



MORE INFORMATION
The RAND function in earlier versions of Excel used a pseudo-random number generation algorithm whose performance on standard tests of randomness was not sufficient. Although this is likely to affect only those users who have to make a large number of calls to RAND, such as a million or more, and not to be a concern for almost every user, the pseudo-random number generation algorithm that is described here was first implemented for Excel 2003. It passes the same battery of standard tests.

The battery of tests is named Diehard (see note 1). The algorithm that is implemented in Excel 2003 was developed by B.A. Wichman and I.D. Hill (see note 2 and note 3). This random number generator is also used in the RAT-STATS software package that is provided by the Office of the Inspector General, U.S. Department of Health and Human Services. It has been shown by Rotz et al (see note 4) to pass the DIEHARD tests and additional tests developed by the National Institute of Standards and Technology (NIST, formerly National Bureau of Standards).

Results in Earlier Versions of Excel
The RAND function in earlier versions of Excel was fine in practice for users who did not require a lengthy sequence of random numbers (such as a million). It failed several standard tests of randomness, making its performance an issue when a lengthy sequence of random numbers was needed.

Results in Excel 2003
A simple and effective algorithm has been implemented. The new generator passes all standard tests of randomness.

Conclusions
Power users of RAND who require lengthy sequences of random numbers are better off with the new generator of Excel 2003. Other users should be undeterred from using RAND in earlier versions of Excel.

