Microsoft Office Tutorials and References

In Depth Information

**Generating and Using Random Numbers**

The all-purpose RAND function

The RAND function is simple — it takes no arguments and returns a decimal

value between 0 and 1. That is, RAND never actually returns 0 or 1; the value

is always in between these two numbers. The function is entered like this:

=RAND()

The RAND function returns values such as 0.136852731, 0.856104058, or

0.009277161. “Yikes!” you may be thinking. “How do these numbers help if

you need values between 18 and 65?” Actually, it’s easy with a little extra math.

There is a standard calculation for generating random numbers within a

determined range. The calculation follows:

= RAND() * (high number - low number) + low number

Using 18 and 65 as a desired range of numbers, the formula looks like

=RAND()*(65-18)+18. Some sample values returned with this formula

follow:

51.71777896

27.20727871

24.61657068

55.27298686

49.93632709

43.60069745

Almost usable! But what about the long decimal portions of these numbers?

Some people lie about their ages, but I’ve never heard someone say he’s 27.2

years old!

All that is needed now for this 18-to-65 age example is to include the INT

or ROUND function. INT simply discards the decimal portion of a number.

ROUND allows control over how to handle the decimal portion.

The syntax for using the INT function with the RAND function follows:

= INT((high number – low number + 1) * RAND() + low

number)