Home » Excel-Formulas » Excel-Random-Number

Excel Random Number Generation

Random Number Functions:
RAND
RANDBETWEEN

This page gives examples of how to use the Excel random number generation functions to perform the following random selections:

Select a Random Decimal Value Between 0 and 1
Select a Random Integer Between Two Supplied Integers
Select a Random Decimal Between Two Specified Values
Select a Random Value From a List

Select a Random Decimal Value Between 0 and 1

If you want to generate a random decimal value between 0 and 1, simply used the Excel Rand function by entering the following formula into any cell of your worksheet:

=RAND()

This function will generate a different random decimal, between 0 and 1 every time your worksheet re-calculates.

Select a Random Integer Between Two Supplied Integers

The Excel Randbetween function is used to generate a random integer between two supplied integers. For example:

=RANDBETWEEN( 0, 10 )

generates a random integer between 0 and 10.

Select a Random Decimal Between Two Specified Values

If you want to generate a random decimal value between any two values, A and B you can use the following formula (which uses the Excel Rand function):

=A+(B-A)*RAND()

Example 1

The following formula generates a random decimal value between the values 5 and 10:

=5+(10-5)*RAND()

Example 2

The formula below generates a random decimal value between the values 0.5 and 1.2:

=0.5+(1.2-0.5)*RAND()

Select a Random Value From a List

  A
1 Adam
2 Bill
3 Colin
4 Harry
5 John
6 Ken
7 Lee
8 Mike

If you want to select a value at random from a list, this can be done with the use of the Excel Randbetween and Index functions.

For example, if you want to select a name at random from the values in cells A1 - A8 of the above spreadsheet on the right, this can be done using the following formula:

=INDEX( A1:A8, RANDBETWEEN( 1, 8 ) )

In the above formula:

  • The Randbetween function returns a random integer between 1 and 8.
  • The Index function returns a reference to the n'th cell in the range A1 - A8 (where n is the random integer returned by the Randbetween function).

Note that the Excel Rand and Randbetween functions return different values every time the spreadsheet recalculates. To force a recalculation at any time, press the F9 key.

Return to the Excel Formulas Page
Return to the ExcelFunctions.net Home Page