Excel: Random Number Generation

ID: Q86523

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel for Windows, versions 2.0, 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 1.0, 2.20, 3.0, 4.0, 5.0
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

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