Excel: Random Number Generation

ID: Q86523

The information in this article applies to:

SUMMARY

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


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.


MORE INFORMATION

Computers cannot generate truly random numbers. Instead, they return a series of numbers based on a mathematical relationship that simulates "randomness." The "randomness" exhibited by the numbers is a function of the mathematical formula used by the random number generator.

In Microsoft Excel for Windows, adding randomize=1 to the [Microsoft Excel] section of the appropriate .INI file for your version of Microsoft Excel will cause "r" to be determined from the system clock (which adds a further degree of randomness to the numbers generated). 


   For this version         The name of your
   of Microsoft Excel       .INI file is this
   -----------------------------------------

   5.0                      EXCEL5.INI
   4.0                      EXCEL4.INI
   3.0                      EXCEL.INI


Note that there is no such setting with Microsoft Excel for OS/2 or Microsoft Excel for the Macintosh.

NOTE: Excel 5.0 defaults to the RANDOMIZE=1 setting. Previous versions of Microsoft Excel defaulted to RANDOMIZE=0.

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 rand random algorithm 


Keywords          : xlformula 
Version           : MACINTOSH:1.0,2.20,3.0,4.0,5.0,98; WINDOWS:2.0,3.0,4.0,5.0,7.0,97; winnt:5.0
Platform          : MACINTOSH WINDOWS winnt 
Issue type        : kbinfo

Last Reviewed: June 28, 1999