Microsoft Office Tutorials and References

In Depth Information

**Using RAND and RANDBETWEEN to Generate Random Numbers and Data**

=RAND() generates a random decimal between 0 and 0.999988425925926. Whether

you are a teacher trying to randomly assign the order for book report

presentations, or the commissioner of a fantasy football league trying to

figure out the draft sequence, =RAND() can help.

If you want to use RAND to generate a random number but don
’
t want the num-

bers to change every time the cell is calculated, you can enter =RAND() in the

formula bar and then press F9 to change the formula to a random number.

To generate a random number greater than or equal to 0 but less than 100, you

can use RAND()*100.

To generate a random sequence for a list, you select a blank column next to

your data and enter =RAND()

=RAND() in the column. Every time you press the F9 key,

the column generates a new set of random numbers. You might want to agree

up front with the draft participants that you will press F9 three times to

randomize the list and then convert the formulas to values. To do so, follow

these steps:

11. Enter the heading Random

Random in row 1 next to your data.

22. Enter =RAND()

=RAND() in cell B2.

33. Move the cell pointer to cell B2 and double-click the fill handle.

44. Turn off automatic calculation by using Formulas, Calculation Op-

tions, Manual. This prevents the RAND() functions from recalculat-

ing after you sort in step 7.

55. Press the F9 key three times.

66. Choose one cell in column B.

77. From the Data tab, click the AZ button to sort ascending. The new se-

quence of items in column A is a random sequence (see
Figure 11.20
)
.