Description of the RAND function in Excel
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).
- The tests were developed by Professor George Marsaglia, Department of Statistics, Florida State University and are available at the following Web site:
- Wichman, B.A. and I.D. Hill, Algorithm AS 183: An Efficient and Portable Pseudo-Random Number Generator, Applied Statistics, 31, 188-190, 1982.
- Wichman, B.A. and I.D. Hill, Building a Random-Number Generator, BYTE, pp. 127-128, March 1987.
- Rotz, W. and E. Falk, D. Wood, and J. Mulrow, A Comparison of Random Number Generators Used in Business, presented at Joint Statistical Meetings, Atlanta, GA, 2001.
C IX, IY, IZ SHOULD BE SET TO INTEGER VALUES BETWEEN 1 AND 30000 BEFORE FIRST ENTRYIX = MOD(171 * IX, 30269)IY = MOD(172 * IY, 30307)IZ = MOD(170 * IZ, 30323)RANDOM = AMOD(FLOAT(IX) / 30269.0 + FLOAT(IY) / 30307.0 + FLOAT(IZ) / 30323.0, 1.0)
Because RAND produces pseudo-random numbers, if a long sequence of them is produced, eventually the sequence will repeat itself. Combining random numbers as in the Wichman-Hill procedure guarantees that more than 10^13 numbers will be generated before the repetition begins. Several of the Diehard tests produced unsatisfactory results with earlier versions of RAND because the cycle before numbers started repeating was unacceptably short.
Results in Earlier Versions of ExcelThe 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 2003A simple and effective algorithm has been implemented. The new generator passes all standard tests of randomness.
ConclusionsPower users of RAND who require lengthy sequences of random numbers are better off with the new generator of Excel 2003 and later versions. Other users should be undeterred from using RAND in earlier versions of Excel.
Article ID: 828795 - Last Review: 09/18/2011 22:59:00 - Revision: 7.0
- kbfuncstat kbfunctions kbinfo KB828795