Problem: You want to create a range of random numbers in order to illustrate a concept in a class. As shown in Fig. 293, you want to fill in random numbers in columns C through E.
Note: Click any image below for a larger view.
Strategy: Use the RAND function. This function will return a random number between 0 and 0.99999.
You will often need to create random numbers between a range of digits. In Fig. 294, you might want sales between 1 and 100 for each cell. Use a formula such as =RAND()*100 to return figures between 0 and 99.999. To return just integers, use the INT function to return the integer portion. =INT(RAND()*100) will return numbers such as these shown in Fig. 295.
Note that the formula is returning values from 0 to 99. If you really want values from 1 to 100, then use =INT(RAND()*100)+1. See Fig. 296.
Additional Information: Every time that you hit F9 or enter a new value in the worksheet, the random numbers will change. You might want to change the formulas to values to freeze the random numbers. Select the range of random numbers. Use Edit – Copy, and then use Edit – Paste Special – Values to convert formulas to numbers.
Summary: The RAND function can be used to return random numbers.
Commands Discussed: Edit – Paste Special – Values
Functions Discussed: =INT(); =RAND()
See all Microsoft Excel tips