Random numbers are numbers that are selected within a range of values with no specific order or condition. In Google Sheets, random numbers are generated with the use of functions and formulas.
These functions are:
- RAND
- RANDBETWEEN
- Use of an add-on.
The functions operate similarly to each other but differ in their required parameters.
In this guide, I’ll show you how to apply these functions and their formulas to generate random numbers in google sheets.
RANDBETWEEN Function To Generate Random Numbers in Google Sheets
First, we have the RANDBETWEEN function. This function generates a random number that falls within a range of values. The parameters in this range are called upper and lower limits.
The RANDBETWEEN function takes the syntax below.
=RANDBETWEEN(low, high)
Where;
- The low argument is the minimum value in the given range. The random number generated could be greater than or equal to the lower limit.
- The high argument is the maximum value in the given range. The random number generated should be lesser than or equal to the upper limit.
The upper and lower limits don’t necessarily specify the size of the integers.
How to Use the RANDBETWEEN Function in Google Sheets
In the sample worksheet below, I’ll generate a random number from numbers 1 to 20. To do this, you’ll have to follow these quick steps below.
1. Select an empty cell where you want the returned value entered. Here, I selected cell B2.
2. In this cell, enter the formula =RANDBETWEEN(1, 20).
3. Hit the Enter key. A random number would be generated and returned to the cell.
Also, if you want a less direct approach, you can enter the other cells and use their cell references as the parameters of the formula.
Therefore, the formula would be reentered as =RANDBETWEEN(A2, A3)
If any changes are made to the values in the cell references, the RANDBETWEEN function would automatically recalculate and update the results. This also applies to the RAND function.
Problem with the RANDBETWEEN function.
Using the RANDBETWEEN function is a great choice since it’s quite efficient and easy but there are a few drawbacks to this method.
The RANDBETWEEN function works preferably with large ranges to produce more distinct results. However, it doesn’t include decimal integers.
The RANDBETWEEN function cannot return decimal random numbers but the RAND function can.
RAND Function to Generate Random Numbers in Google Sheets
This is another great way to generate random numbers. The RAND function allows you to generate random decimal integers.
The range is from 0 to 1. That implies the generated random numbers are greater than or equal to 0 and less than 1.
The RAND function takes the syntax below.
=RAND()
There are no required parameters in its formula.
Using the RAND Function in Google Sheets.
In the sample worksheet below, I’ll generate three random numbers from numbers 0 to 1.
Follow the steps below to generate random numbers.
1. Select an empty cell. This is where the random number would be inserted.
2. In the cell, enter the formula =RAND().
3. Click on the Enter key and a random decimal number is returned.
4. To generate two more random decimal numbers, use the drag-down fill icon to copy the formula into the cells. It automatically calculates and two more decimal numbers are entered into the cells.
You can also make your random numbers more dynamic by modifying and adding other functions and integers to the RAND formula.
For instance, you also want to sum the random number with some other digits. You can make a few adjustments to the formula like this.
=RAND() * (6 – 1) + 2
This makes the RAND function more dynamic than the RANDBETWEEN function. Since it only returns decimal numbers, repetitions of a random number are very rare.
Differences between RAND & RANDBETWEEN
Although these functions are quite similar, they have some distinct differences. These differences are represented in a tabular form below.
| RANDBETWEEN | RAND |
| The RANDBETWEEN formula has two parameters in its formula syntax. | The RAND formula doesn’t have any parameters in its syntax. It automatically takes 0 and 1 as its parameters. |
| RANDBETWEEN is prone to a repetition of random numbers. | RAND produces more distinct and unique numbers. |
| RANDBETWEEN works with only whole numbers. | RAND returns random numbers in decimal. |
Both functions are very volatile. They would recalculate frequently if you refresh your worksheet, change certain values and even when you are opening the worksheet.
This could cause errors if you have other functions in your worksheet.
Altering the Refresh Rate of RAND and RANDBETWEEN.
Due to the ever-changing nature of the RAND and RANDBETWEEN functions, the generated random numbers tend to change over a certain time.
These changes occur when an adjustment is made to the worksheet and when the page is refreshed.
This could affect your data if you have other formulas that depend on these integers. To alter the refresh rate of these functions, take these easy steps.
1. Move to the toolbar and select File.
2. From the list of options, select Settings.
3. The Settings tab is opened. Here, click on the Calculation section.
4. Under the ‘Recalculation’ option, click on the drop-down arrow.
5. Here, you can select the frequency and timing of the functions.
6. Once you’ve selected a frequency, click on Save Settings.
Generate Random Numbers with an Add-On.
This is another quick way to generate random numbers in Google Sheets. Add-ons are employed applications that improve the workforce of Google Sheets.
Here, we’ll use an add-on by Ablebits to generate random numbers. But first, we need to install this add-on. To do this, we would follow the steps below.
1. On the toolbar, click on Extensions.
2. From the options below, select Add-ons.
3. Then, click on Get Add-ons.
4. You’ll be redirected to the Google Workspace Marketplace website. In the search box, enter Random Generator.
5. From the results provided, select the Random Generator Add-on by Ablebits from the results.
6. Click on Install.
7. Then you’d have to grant certain permissions for the add-on to operate.
After you’ve done this, your add-on is ready to start.
1. Go back to your worksheet and click on Extensions.
2. Select the Random Generator Add-ons by Ablebits.
3. Click on Start.
4. The add-on tab is opened.
Here, you can perform other tasks related to selecting random numbers.
They include:
- Generation of whole random numbers or decimal random numbers with lower and upper limits.
- Generate unique and distinct values with no repetitions.
- Generate random dates.
- Generate values and select random items from a given list.
- Generate random password formats and orders.
Final Thoughts
Now, we’ve completely covered this topic and you should know how to use these functions and realize their importance. You should also know their unique characteristics and differences.
I hope this article comes in handy and you learnt a thing or two.
