Generate Random Numbers Using Google Sheets RAND Function

Get a random number between 0 and 1

Young male in classroom working on a laptop

NESA by Makers / Unsplash

One way to generate random numbers in Google Sheets is with the RAND function. By itself, the function generates a limited range when it comes to generating random numbers, but by using RAND in formulas and by combining it with other functions, the range of values can easily be expanded.

  • By specifying the high and low values of a range, RAND can be made to return random numbers within a specified range such as 1 and 10 or 1 and 100.
  • The function's output can also be reduced to integers by combining the function with the TRUNC function, which truncates or removes all decimal places from a number.

In Google Sheets, when generating a random value between 0 and 1, the RAND function returns a random number between 0 inclusive and 1 exclusive. While it is usual to describe the range of values generated by the function as being from 0 to 1, in truth, it is more exact to say the range is between 0 and 0.99999999...

The formula that returns a random number between 1 and 10 returns a value between 0 and 9.99999...

The RAND Function Syntax

Generate Random Numbers with Google Spreadsheets' RAND Function

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 RAND function is:

= RAND ( )

Unlike the RANDBETWEEN function, which requires high- and low-end arguments to be specified, the RAND function accepts no arguments.

The RAND Function and Volatility

 The RAND function is a volatile function that, by default, changes or recalculates every time the worksheet changes, and these changes include actions such as the addition of new data.

Furthermore, any formula that depends either directly or indirectly on a cell containing a volatile function also recalculates every time a change in the worksheet occurs.

Therefore, in worksheets that contain large amounts of data, volatile functions should be used with caution, as they can slow down the program's response time due to the frequency of recalculations.

Generating New Random Numbers With Refresh

Because Google Sheets is an online spreadsheet program, the RAND function can be forced to generate new random numbers by refreshing the screen using the web browser refresh button. 

A second option is to press the F5 key on the keyboard which also refreshes the current browser window.

Changing the Refresh Frequency of RAND

Spreadsheet Settings

In Google Sheets, the frequency with which RAND and other volatile functions recalculate can be changed from the default on change to:

  • On change and every minute.
  • On change and every hour.

Steps for altering the refresh rate are:

  1. Select the File menu to open the menu's list of options.
  2. Select Spreadsheet Settings in the list to open the Spreadsheet Settings dialog box.
  3. Under the Recalculation section of the dialog box, choose the current setting, such as on change to show the complete list of recalculation options.
  4. Select the desired recalculation option in the list.
  5. Select the Save Settings button to save the change and return to the worksheet.

Entering the RAND Function

Entering RAND function in Google Sheets

Since the RAND function takes no arguments, it can be entered into any worksheet cell by typing:

= RAND ( )

Alternatively, the function can also be entered using Google Sheets' auto-suggest box that pops up as the name of the function is typed into a cell. The steps are:

  1. Select a cell in a worksheet where the function's results are to be displayed.
  2. Type the equal sign ( = ) followed by the name of the function RAND.
  3. As you type, the auto-suggest box appears with the names of functions that begin with the letter R.
  4. When the name RAND appears in the box, select the name to enter the function name and an open round bracket in the selected cell.
  5. A random number between 0 and 1 appears in the current cell.
  6. To generate another, press the F5 key on the keyboard or refresh the browser.

When you select the current cell, the complete function = RAND () appears in the formula bar above the worksheet.

Generating Random Numbers Between 1 and 10 or 1 and 100

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

The general form of the equation used to generate a random number within a specified range is:

=RAND() * (High - Low) + Low

Here, High and Low signify the upper and lower limits of the desired range of numbers.

To generate a random number between 1 and 10, enter the following formula into a worksheet cell:

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

To generate a random number between 1 and 100 enter the following formula into a worksheet cell:

=RAND() * (100 - 1) + 1

Generating Random Integers Between 1 and 10

=TRUNC (RAND() * (10 - 1) + 1)

To return an integer — a whole number with no decimal portion — the general form of the equation is:

=TRUNC ( RAND() *(High - Low) + Low)

To generate a random integer between 1 and 10, enter the following formula into a worksheet cell:

=TRUNC (RAND() * (10 - 1) + 1)