|
Suppose you're creating an Excel worksheet to perform various kinds of data analysis. You may not have any real data yet, but you can generate random numbers to test the formulas and charts in the worksheet. Random numbers are, by definition, unpredictable. That is, given a series of random numbers, you can't predict the next number from what has come before. Random numbers are quite useful for trying out formulas and calculations.
For example, an actuary may want to test some calculations based on a distribution of people's ages. Random numbers that vary between 18 and 65 could be used for this task. You don't have to manually enter fixed values between 18 and 65 because Excel can generate them automatically with the 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 between these two numbers. The function is entered like this:
=RAND()
The RAND function returns values like 0.136852731, 0.856104058, and 0.009277161. So 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:
=RAND() * (high number - low number) + low number
Using 18 and 65 as a desired range of numbers, the formula looks like this:
=RAND()*(65-18)+18
This formula returns values like 51.71777896 and 27.20727871. Almost usable! But what about the long decimal portions of these numbers?
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)
The syntax for using the ROUND function with the RAND function follows:
=ROUND(RAND() * (high number - low number) + low number,0)
Using one of these formulas to produce random numbers ensures that the numbers 18 and 65 are possible outcomes.
Try it yourself! Here's how to use RAND and INT together:
1. Position the pointer in the cell where you want the results displayed.
2. Enter =INT(( to begin the formula.
3. Click the cell that has the highest number to be used, or enter such a value.
4. Enter – (a minus sign).
5. Click the cell that has the lowest number to be used, or enter such a value.
6. Enter +1) * RAND() + .
7. Click again on the cell that has the lowest number to be used, or enter the value again.
8. Type a ) and press Enter.
A random number, somewhere in the range of the low and high number, is returned.
 | The RAND function is subject to the recalculation feature built into worksheets. In other words, each time the worksheet calculates, the RAND function is rerun and returns a new random number. You can check the calculation setting by looking at the Formulas tab in the Excel Options dialog box. On a setting of Automatic, the worksheet recalculates with every action. The random generated numbers keep changing, which can become quite annoying — not to mention the fact that it can ruin your work! |
 | 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. This tells Excel to calculate the formula and enter the returned random number as a fixed number instead of as a formula. If you press the Enter key or finish the entry in some way other than using the F9 key, you'll have to enter it again. |
| 
