The generation of random numbers can be incredible useful in many varied applications and excel (2003, 2007 and 2010) can create random numbers very easily. There are many varied uses for random numbers from simple applications such as selecting random numbers for the lottery or for much more complex application as unbiased sampling plans.
The most simplistic random number generation in excel is the RANDBETWEEN statement which generate a random number in the range you specify – perfect to select the lottery numbers. The statement to write to choose a random number between 1 and 49 would be: = RANDBETWEEN (1,49).
Copy the number of random numbers required. The random numbers are updated when any change is made to the excel sheet.
The next method to generate random numbers in excel is to use the RAND statement. This creates a random number between 0 and 1. Strangely the statement requires a bracket to be opened and closed to function. =RAND()
To randomly generate a number from 0 to X, multiply the random number by X.
In the following example a random number between 0 and 100 is generated.
The number of decimal places quoted can be adjusted. This is done by adjusting the number of decimal places in Format cell.
To do this; Select the cell to be altered, right click and selecting “Format Cells”. In the pop up box select the number tab and adjust the number of decimal spaces in the number column. See images below:
In this example a random number was generated between 0 and 100 and then adjusted to 2 decimal places.
The random number can be in a specific range (X to Y) by using the following statement.
=RAND()*(Y – X) + X
In this example a random number was generated between 30.0 to 60.0 1 decimal place.
Related Pages and Random Possibly Useful Guides