Microsoft Office Tutorials and References
In Depth Information
Generating and Using Random Numbers
number to the nearest integer, up or down to whichever integer is closest to
Random values are volatile. Each time a worksheet is recalculated, the random
values change. This behavior can be avoided by typing the formula directly
into the Formula Bar, pressing the F9 key, and then pressing the Enter key.
A last but not insignificant note about using the RAND function: It is subject
to the recalculation feature built in to worksheets. In other words, each time
the worksheet calculates, the RAND function is rerun and returns a new
random number. The calculation setting in your worksheet is probably set
to automatic. You can check this by looking at the Formulas tab in the Excel
Options dialog box. Figure 8-1 shows the calculation setting. On a setting of
Automatic, the worksheet recalculates with every action. The random
generated numbers keep changing, which can become quite annoying if this is not
what you intended to have happen. However, I bet you did want the number
to change, otherwise why use something “random” in the first place!
Luckily, you can generate a random number but have it remain fixed
regardless of the calculation setting. The method is to type the RAND function, along
with any other parts of a larger formula, directly into the Formula Bar. After
you type your formula in, press the F9 key, and then press the Enter key. This
tells Excel to calculate the formula and enter the returned random number as
a fixed number instead of a formula. If you press the Enter key or finish the
entry in some way without pressing the F9 key, you’ll have to enter it again.