Google Spreadsheets' RAND Function: Generate Random Numbers

of 01

Generate a Random Value Between 0 and 1 with the RAND Function

Generate Random Numbers with Google Spreadsheets' RAND Function
Generate Random Numbers with Google Spreadsheets' RAND Function.

One way to generate random numbers in Google Spreadsheets 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 combing it with other functions, the range of values, as shown in the image above 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.

Note: According to Google Spreadsheets' help file, the RAND function returns a random number between 0 inclusive and 1 exclusive.

What this means is that 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....

By the same token, the formula that returns a random number between 1 and 10 actually returns a value between 0 and 9.999999....

The RAND Function's 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.

Further, 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.

Therefore, in worksheets  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.

Generating New Random Numbers with Refresh

Since Google Spreadsheets is an online program, the RAND function can be forced to generate new random numbers by refreshing the screen using the web browsers refresh button. Depending on the browser used, the refresh button is usually a circular arrow located near the browser's address bar.

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

Changing RAND's Refresh Frequency

In Google Spreadsheets, 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. Click on the File menu to open the menu's list of options
  2. Click on Spreadsheet Settings in the list to open the Spreadsheet Settings dialog box
  3. Under the Recalculation section of the dialog box, click on the current setting - such as on change to show the complete list of recalculation options
  4. Click on the desired recalculation option in the list
  5. Click on the Save Settings button to save the change and return to the worksheet

RAND Function Examples

Below are listed the steps required to reproduce the examples shown in the image above.

  1. The first enters the RAND function by itself;
  2. The second example creates a formula that generates a random number between 1 and 10 or 1 and 100;
  3. The third example generates a random integer between 1 and 10 using the TRUNC function.

Example 1: Entering the RAND Function

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

= RAND ( )

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

  1. Click on 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, click on the name with the mouse pointer to enter the function name and an open round bracket into the selected cell
  5. A random number between 0 and 1 should appears in the current cell
  6. To generate another, press the F5 key on the keyboard or refresh the browser
  7. When you click on current cell, the complete function = RAND ( ) appears in the formula bar above the worksheet

Example 2: 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

where 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

Example 3: 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)