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…
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:
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: 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)
