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.
Search JabSto ::




Custom Search