Generate Random Numbers Using Google Sheets RAND Function

Get a random number between 0 and 1

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

How the RAND Function Works

By specifying the high and low values of a range, RAND can 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...

Learn about Spreadsheet Google Random Seed
NESA by Makers / Unsplash

The RAND Function Syntax

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

In Google Sheets, you can change the frequency with which RAND and other volatile functions recalculate 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.

    Generate Random Numbers Using Google Sheets RAND Function
  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.

    Spreadsheet settings in Google Sheets.
  4. Select the desired recalculation option on the list.

    Spreadsheet settings in Google Sheets.
  5. Select the Save Settings button to save the change and return to the worksheet.

    Save settings.

Entering the RAND Function

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

= RAND ( )

Alternatively, you can also enter the function 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. As you type, the auto-suggest box appears with the names of functions that begin with the letter R. 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.

    RAND in Google Sheets
  3. A random number between 0 and 1 appears in the current cell. To generate another, press the F5 key on the keyboard or refresh the browser.

    Random number in Google Sheets.

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

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

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)

Was this page helpful?