Page 365 - Excel for Scientists and Engineers: Numerical Methods

342 EXCEL: NUMERICAL METHODS

r = fractional part of (9821 x s + 0.21 1327)

where s = the previous random number
In an effort to increase the "randomness," Microsoft later provided a patch
that caused r to be determined from the system clock (which added a further
degree of randomness to the numbers generated). But because these pseudo-
random numbers are produced by a mathematical algorithm, if a long sequence
of them is produced, eventually the sequence will repeat itself. Statistical tests
on series of random numbers produced by the earlier version of RAND revealed
that the cycle before numbers started repeating was unacceptably short, in the
vicinity of one million.
In the improved random number generator used in Excel 2003, three sets of
random numbers are generated. Three of these random numbers are summed,
and the fractional part of the sum is used as the random number. By this
procedure, it is stated that more than 1013 numbers will be generated before the
repetition begins.
The random-number algorithm in Excel 2003 was developed by B. A.
Wichman and I. D. Hill ("Algorithm AS 183: An Efficient and Portable Pseudo-
Random Number Generator," Applied Statistics, 31, 188-190, 1982; "Building a
Random-Number Generator," BYTE, pp. 127-128, March 1987). This random
number generator is also used in a software package that is provided by the U.S.
Department of Health and Human Services. It has been shown to pass tests
developed by NIST (National Institute of Standards and Technology).

Using Random Numbers in Excel
You can use random numbers in many ways, for example: to add "noise'' to a
signal generated by a formula, to select items randomly from a list, or to perform
a simulation by using the Monte Carlo method. These and some other uses of
random numbers will be described in following sections.
Excel provides several ways to generate random numbers. The worksheet
function RAND returns a random real number greater than or equal to 0 and less
than 1. RAND is a volatile function; that is, a new random number is returned
every time the worksheet is calculated. You can test this, after entering =RAND()
in a cell, by pressing F9 (Calculate Now) or by typing anything (even a space
character) in a cell and pressing the Enter key. You will see that the value
returned by the RAND function changes.
The fact that random numbers are recalculated every time you do just about
anything on a spreadsheet can sometimes be problematic, especially if your
spreadsheet contains large ranges of such numbers. In the old days of 133-MHz
computers, there could be a delay of several seconds while the spreadsheet