Generate Random Numbers with Excel's RANDBETWEEN Function

01
of 01

Generate Random Integers Between any Two Numbers

Finding Random Integers with Excel's RANDBETWEEN Function
Finding Random Integers with Excel's RANDBETWEEN Function. © Ted French

Related Information:  Use Excel's RAND function to generate random numbers including decimal values.

simulated coin flip Excel

=TEXT(RANDBETWEEN(0,1),"""Heads"";;""Tails""")

RANDBETWEEN Function Overview

The RANDBETWEEN function can be used to generate random integers (whole numbers only) between a range of values in an Excel worksheet. The range for the random number is specified using the function's arguments.

Whereas the more commonly used RAND function will return a decimal value between 0 and 1, RANDBETWEEN can generate an integer between any two defined values - such as zero and ten or one and hundred.

Uses for RANDBETWEEN include creating specialty formulas such as the coin toss formula shown in row 4 in the image above and dice rolling simulations.

The RANDBETWEEN Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the RANDBETWEEN function is:

= RANDBETWEEN ( Bottom , Top )

Bottom - (required) the lowest possible integer the function is to return as a result
- the actual integer can be entered for this argument or it can be a cell reference to the location of the data in the worksheet.

Top - (required) the highest possible integer the function is to return as a result
- the actual integer can be entered for this argument or it can be a cell reference to the location of the data in the worksheet.

#NUM! error: If the Bottom argument is a larger number than the Top argument, the RANDBETWEEN function will return a #NUM! error value in the cell where the function is located as shown in row 5 in the image above.

Example Using Excel's RANDBETWEEN Function

The steps listed below cover how to get the RANDBETWEEN function to return a random integer between one and 100 as shown in row 3 in the image above.

Entering the RANDBETWEEN Function

Options for entering the function and its arguments include:

  1. Typing the complete function such as: =RANDBETWEEN (1,100) or = RANDBETWEEN ( A3 , A3 ) into a worksheet cell;
  2. Selecting the function and arguments using the function's dialog box.

Although it is possible to just type the complete function in by hand, many people find it easier to use the dialog box as it takes care of entering the function's syntax - such as brackets and comma separators between arguments.

Opening the Dialog Box

To open the RANDBETWEEN function dialog box,

  1. Click on cell C3 to make it the active cell - the location for the RANDBETWEEN function will be located
  2. Click on the Formulas tab of the ribbon
  3. Click on the Math & Trig icon to open the function drop down list
  4. Click on RANDBETWEEN in the list to open the function's dialog box

The data that will be entered into the blank rows in the dialog box will form the function's arguments.

Entering the RANDBETWEEN Function's Arguments

  1. Click on Bottom line of the dialog box
  2. Click on cell A3 in the worksheet to enter this cell reference into the dialog box
  3. Click on Top line of the dialog box
  4. Click on cell B3 in the worksheet to enter the second cell reference
  5. Click OK to complete the function and return to the worksheet
  6. A random number between 1 and 100 should appear in cell C3
  7. To generate another random number, press the F9 key on the keyboard which causes the worksheet to recalculate.
  8. When you click on cell C3 the complete function = RANDBETWEEN ( A3 , A3 ) appears in the formula bar above the worksheet

The RANDBETWEEN Function and Volatility

Like the RAND function, RANDBETWEEN is one of Excel's volatile functions. What this means is that:

  • The function recalculates - produces a new random number - every time the worksheet changes, and this includes actions such as adding new data or formulas.
  • Any formula that depends - either directly or indirectly - on a cell containing a volatile function will also recalculate every time a change in the worksheet occurs.
  • In worksheets or workbooks containing large amounts of data, volatile functions should be use with caution as they can slow down the program's response time due to the frequency of recalculations.

Recalculation Cautions

These functions dealing with randomness will return a different value upon each recalculation. This means that every time that a function is evaluated in a different cell, the random numbers will be replaced by updated random numbers.

For this reason, if a particular set of random numbers is to be studied later, it would be worthwhile to copy these values, and then paste these values into another part of the worksheet.