Microsoft Office Tutorials and References

In Depth Information

**Generating random numbers**

Generating random numbers

The built-in random-number function, RAND, generates a uniform distribution of random

real numbers from 0 to 1. In other words, all values from 0 to 1 share the same probability

of being returned by a set of formulas based on the RAND function. Because the sample is

relatively small, the distribution is by no means perfectly uniform. Nevertheless, repeated

tests demonstrate that the RAND function doesn’t favor any position within its spectrum

of distribution. For more information, see “The RAND and RANDBETWEEN functions” in

Chapter 14.

TROUBLESHOOTING

Random numbers keep changing

The RAND function is one of Excel’s
volatile
functions—that is, it recalculates every

time the worksheet recalculates, which happens when you open the worksheet as well

as every time you make an entry or edit data in a cell. If you want to generate a set of

random numbers and then “freeze” them, select all the RAND formulas in your

worksheet, and then press Ctrl+C to copy them. Click the Paste button on the Home tab

on the ribbon, and click Paste Values to replace the volatile formulas with fixed values.

Or, instead of using the RAND function, use the Random Number Generation tool

(described next), which produces constants instead of formulas.

The Random Number Generation tool creates sets of random numbers that are not

uniformly distributed. You can then use the Histogram tool to sort and plot the results for

Monte Carlo decision analysis and other kinds of simulations. Six distribution types are

available: Uniform, Normal, Bernoulli, Binomial, Poisson, and Discrete (user-defined). In

addition, you can select Patterned in the Distribution list to create nonrandom numbers at

specified intervals. Click the Data Analysis button on the Data tab, select Random Number

Generation, and then click OK to display a dialog box like the one shown in Figure 17-13.

Here are a few important points about using the Random Number Generation tool:

In the Number Of Variables and Number Of Random Numbers text boxes, you

indicate how many columns of numbers you want and how many numbers you want in

each column. For example, if you want 10 columns of 100 numbers each, specify
10

in the Number Of Variables text box and
100
in the Number Of Random Numbers

text box.

●

You can also specify a seed value. However, each time you generate a

randomnumber set with a particular distribution type and use the same seed value, you get

the same sequence of numbers; therefore, you should specify a seed value only if you

need to be able to reproduce a random-number sequence.

●