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:
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
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
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