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

the number.

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.

Figure 8-1:

Setting

worksheet

calculation

options.