Generating Random Numbers

Problem

You'd like to compute random numbers between specific upper and lower bounds and you're not sure how to do so in Excel.

Solution

Use Excel's built-in spreadsheet function RAND or VBA's Rnd function.

Discussion

RAND generates an evenly distributed random number between 0 and 1. Its syntax is =RAND( ) with no arguments. When this function is used in a spreadsheet, its value changes every time the spreadsheet is recalculated. For a random number that won't be recalculated, enter =RAND( ) in the formula bar and then press F9. This will enter a random number between 0 and 1 in the selected cell, instead of entering the formula =RAND( ). Therefore, the random value will remain unchanged when the spreadsheet is recalculated.

To compute a random number between two specific values, use the formula =RAND( )*(MaxVal-MinVal)+MinVal, where MinVal and MaxVal are the two values between which you want the random number to be generated. The result will be a real number. To generate a random integer, use =ROUND(RAND( )*(MaxVal-MinVal)+MinVal, 0).

You can use the VBA function Rnd in a similar manner. For example, the code x = (MaxX-MinX+1)*Rnd+MinX generates a random number between MinX and MaxX. In VBA, you should seed the random number generator by making a call to the Randomize function prior to using the Rnd function.

See Also

See Recipe 13.4 for an example that uses random numbers.

Generating random numbers can also be useful when selecting random samples from some population. Recipe 13.7 shows how to select random samples from a population where the probability of selecting any given sample is not uniform.

In cases where you need to draw samples from a finite population of discrete values, you should consider the Sampling tool . This tool is discussed in Recipe 5.9.