RANDBETWEEN allows you to generate a number randomly from a given limit between two numbers. This article will focus on the RANDBETWEEN function and take a look at the syntax for it, reasons for using RANDBETWEEN instead of RAND, and a few examples of the function.
The Syntax for RANDBETWEEN
Before we look at the function in action, it’s important to understand the syntax of the formula. It works similar to RANDBETWEEN in Excel. Here is how to use it in Google Sheets:
This syntax is relatively simple compared to other functions and only needs two arguments to work. These are:
lower: This is the lower limit of the range. It specifies that the number to be generated should be equal to or greater than the defined parameter. upper: This is the upper limit of the range. It specifies that the number to be generated should be equal to or less than the defined parameter.
Decimal values can be used for the lower and the upper parameters. Using these values means that the upper limit will be rounded off to the lower whole number. For example, if the upper limit is 10.4, it will be rounded off to 10. Similarly, if the lower limit is defined as 2.6, the number will be rounded to 3.
RANDBETWEEN vs. RAND
Although RAND and RANDBETWEEN work alike and can be used for similar purposes, like building complex random number generators in Google Sheets, they are not the same.
RANDBETWEEN is far less likely to produce fully unique numbers for a smaller range when compared to the RAND formula. RAND can return decimal values, while RANDBETWEEN returns integer values. The RAND formula does not take any parameters, while RANDBETWEEN requires two parameters to work. The parameters specify the values’ upper and lower limit of the values to be generated. The RAND formula can generate a number between 0 and 1. On the other hand, RANDBETWEEN can be used to generate a number within a limit defined by the user.
RANDBETWEEN Examples in Google Sheets
To understand the function properly, let’s look at a few examples of the function in action.
Simple RANDBETWEEN
Here is the simplest way to use the RANDBETWEEN function in Google Sheets. In the below example, we will select the lower limit as 1 and the upper one as 100. The function will generate a value between the specified limits.
Here are the steps to perform to do this:
Click and select the cell where you want to enter the formula. Type in an equal symbol and then the name of the formula. Add an opening bracket after this. In this case, it will look like so =RANDBETWEEN( For the first parameter, we will enter the lower limit, which is 1 in this case. Add a comma symbol and type in the second parameter, which is the upper limit. In this case, it is 100. Add a closing bracket to finish the formula. Press the Enter key or click out of the cell to execute the formula.
Nested RANDBETWEEN
In this example, we will look at a nested implementation of the RANDBETWEEN formula. We have 10 names in a column, and we wish to pick out one name randomly. We can do this by combining the INDEX, RANDBETWEEN, and COUNTA formulas.
Here are the steps to perform to do this:
Click and select the cell where you want to enter the formula. Type in an equal symbol and then the name of the first formula. Add an opening bracket after this. In this case, it will look like =INDEX( For the first parameter, we will enter the cell range, which is A2:A11 in this case. Add a comma symbol and type in the second parameter, which is the RANDBETWEEN formula. In this case, it is RANDBETWEEN( Add the first parameter for the formula, which is 1 as the lower limit. For the upper limit, we will use the COUNTA formula to count the number of entries. Here, we write it as COUNTA( Write the cell range as the parameter for COUNTA. Here, we write it as A2:A11. Add three closing brackets to finish the formula (one for each nested function in the formula) Press the Enter key or click out of the cell to execute the formula.
Let’s talk about how the formula works.
First, we use the INDEX formula to return an item from the column at the position denoted by a random number. Then we use the RANDBETWEEN formula to generate the random number between the lower and upper limits. COUNTA is used to find the upper limit of the column.
Things You Should Know About RANDBETWEEN
The RANDBETWEEN formula is a volatile formula. This means that it recalculates whenever you view, edit, or delete a version of your Google Sheet. A recalculation is also done every time you reopen the spreadsheet.
You can also force the function to be recalculated every minute or every hour. To do this, you will have to enable recalculation:
Clicking on File in the top bar of the spreadsheet. In the dropdown menu that opens, click on Settings. A new window will open > switch over to the Calculation tab. Click on the box under Recalculation. Here, you can select when the recalculation should be done. You can choose from On change, On change and every minute, and On change and every hour.
Note that your spreadsheet can become extremely slow if you use this option, especially if you have a lot of formulas or values in the spreadsheet.
Wrapping Up the RANDBETWEEN Function in Google Sheets
To boil it all down, you should use the RANDBETWEEN function in Google Sheets whenever you need to set upper and lower limits for a random number in a formula.
It can easily be used on its own in a cell and then used in a cell reference for another function or nested in a longer formula. In either case, it’s a great essential Google Sheets function to have in your arsenal.
