Excel RAND, RANDBETWEEN & Random Numbers

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.

RANDBETWEEN - Excel

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).

Excel - RAND, RANDBETWEEN, Random Numbers in Excel

Copy the number of random numbers required. The random numbers are updated when any change is made to the excel sheet.

RAND - Excel

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()

RAND Feature in Excel

To randomly generate a number from 0 to X, multiply the random number by X.
=RAND()*X.
In the following example a random number between 0 and 100 is generated.

RAND Feature in Excel

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:

Calculating and Using Random Numbers in Excel - RAND and RANDBETWEEN Excel Calculating and Using Random Numbers in Excel - RAND and RANDBETWEEN Excel Calculating and Using Random Numbers in Excel - RAND and RANDBETWEEN ExcelCalculating and Using Random Numbers in Excel - RAND and RANDBETWEEN Excel

In this example a random number was generated between 0 and 100 and then adjusted to 2 decimal places.

Calculating and Using Random Numbers in Excel - RAND and RANDBETWEEN Excel

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.

Calculating and Using Random Numbers in Excel - RAND and RANDBETWEEN Excel

Related Pages and Random Possibly Useful Guides

Reduce Mouse Clicks, Save Fingers!

Excel Guides - %RSD - Random number - Bullet Points - Adding Line Space - Weather Chart - Joining scatter Points - Centigrade and Fahrenheit Charts - Adding Units to a Function - Add units to a Cell - Loan Amount
Cat Guides - Is a second cat a good idea? - Why Two cats
CV Guides - The Personal Statement
Car Guide - Replace Disk brakes on a Peugeot 307
Photoshop - Black and white images - Gausium Boarder - Home Page
Word - Format Tables easily - Add tick symbol