How to Generate Random Numbers in Google Sheets?

A sequence of numbers is said to be statistically random when it does not contain any recognizable patterns.

In statistics, randomness can be quite crucial. For example, one often needs to use random numbers when selecting random samples, to ensure that the study is conducted without bias.

Unfortunately, expecting non-biased results from computers can be quite far-fetched.

However, spreadsheet software like Google Sheets uses clever techniques and functions to generate random numbers.

In this tutorial, we will take a look at two Google Sheets functions that help generate random numbers:

  • The RAND function
  • The RANDBETWEEN function

Both functions generate random numbers but differ in the type of input they take in and the type of output they return.

Using the RANDBETWEEN Function to Generate Random Numbers in Google Sheets

The RANDBETWEEN function of Google sheets lets you generate random integers within a given range.

Therefore it lets you specify an upper and lower limit and then returns a random number between the two integers.

Syntax of the RANDBETWEEN Function

The syntax for the RANDBETWEEN function is as follows:

=RANDBETWEEN([lower_limit],[upper_limit])

As can be seen from the above syntax the RANDBETWEEN function takes two inputs:

  • lower_limit: This is an integer that demarcates the lower limit of the range. It specifies that the random number generated should be greater than or equal to this parameter.
  • upper_limit: This is an integer that demarcates the upper limit of the range. It specifies that the random number generated should be less than or equal to this parameter.

Note that both upper_limit and lower_limit parameters must contain integer values. This could be a numeric integer value or reference to a cell containing an integer.

It goes without saying that the lower limit value should be less than the upper limit value.

How to Use the RANDBETWEEN Function in Google Sheets

Using the RANDBETWEEN function to generate random numbers is really easy. Let us take an example to see how it works.

In this example we will generate five random numbers between 1 and 10:

  1. Click on a cell in Google Sheets where you want to insert a random number.
  2. Type in the function: =RANDBETWEEN(1, 10)
  3. Press the Return key.
  4. This should display any random integer between 1 and 10 in the same cell.Randbetween five numbers
  5. Since we want to generate 5 random numbers like this, just drag down the fill handle (located at the lower right corner of the cell) till you reach the fifth cell.
  6. You should now have a set of 5 randomly generated numbers between 1 and 10.Five random generated numbers in Google Sheets

You can use this method to generate integers between any range of integer values.

If you want to use an integer value from a different cell in the function, you can enter a reference to that cell as follows:

Randbetween using cell references

Note that the RANDBETWEEN and the RAND function are volatile. This means that these would recalculate in case there are any changes in the worksheet. In case you don’t want these values to recalculate, you can convert these formula results into static values.

Limitations of RANDBETWEEN Function

The RANDBETWEEN function works great when all you need are a bunch of random integers. However, it’s not the most reliable method if you’re looking to generate a set of unique integers.

RANDBETWEEN most probably will give you unique results if you have a really large range and need to generate a small number of random integers.

For example, if you want to generate 10 numbers between 10 and 1000.

Moreover, as mentioned before it only returns integers. So if you want random decimal values, the RANDBETWEEN function will not really serve many purposes.

You can, however, use the RAND function instead of applications like these.

Using the RAND Function to Generate Random Numbers in Google Sheets

The RAND function of Google Sheets lets you generate random decimal numbers between 0 and 1. The range includes the 0 and excludes the 1. In other words, you can generate random numbers that are more than or equal to 0 and less than 1.

Syntax of the RAND Function

The syntax for the RAND function is as follows:

=RAND()

As can be seen from the above syntax the RAND function takes no inputs or parameters.

How to Use the RAND Function in Google Sheets

Using the RAND function to generate random numbers is easy too. Let us take an example to see how it works.

In this example we will generate five random numbers between 0 and 1:

  1. Click on a cell in Google Sheets where you want to insert a random number.
  2. Type in the function: =RAND()
  3. Press the Return key.
  4. This should display a random decimal between 1 and 10 in the same cell.RAND function in Google Sheets
  5. You can either round off the number to the number of decimal places you like or format the cell to display your required number of decimal places.Rounding RAND to 2 decimal places
  6. Since we want to generate 5 random numbers like this, just drag down the fill handle (located at the lower right corner of the cell) till you reach the fifth cell.
  7. You should now have a set of 5 randomly generated decimal numbers between 0 and 1.Decimal random numbers

Although the function generates only decimal numbers between 0 and 1, you can modify the formula to generate both decimal numbers and integers within any range.

For example, if you want to generate random numbers between 1 and 10, you can tweak the original rand formula as follows:

=RAND() * (10 - 1) + 1

In fact, you can use the function to generate numbers within any range by generalizing the above formula as follows:

=RAND() * (upper_limit – lower_limit) + lower_limit

where upper_limit is the maximum value of your required range, and lower_limit is the minimum value.

RAND with upper and lower limit

If you want to generate integer numbers between 1 and 10, instead of decimal, you only need to wrap the above formula inside a TRUNC function as follows:

=TRUNC(RAND() * (upper_limit – lower_limit) + lower_limit)

So, to generate random integers between 1 and 10, your formula would be:

=TRUNC(RAND() * (10 - 1) + 1)

Truncating rand function result

As can be seen, the RAND function is a much more versatile function than RANDBETWEEN.

Moreover, since the RAND function uses the range 0 to 1, there is a good probability that the numbers generated by it will not repeat (at least for 100,000 times!).

Differences between RAND & RANDBETWEEN

To sum up, let us go over the main differences between RAND and RANDBETWEEN:

  • RANDBETWEEN takes two parameters that specify the range within which we want the random number generated. RAND, on the other hand, does not take any parameters. It can generate any number between 0 and 1.
  • RANDBETWEEN returns an integer value while RAND returns a decimal value.
  • If the range is small, RANDBETWEEN is less likely than RAND to generate unique random numbers.

Altering the Refresh Rate of RAND and RANDBETWEEN

Both RAND AND RANDBETWEEN are volatile functions. This means the number generated by these two functions don’t remain for long.

They recalculate a new value whenever there is a change in the sheet or when the sheet is re-loaded.

Moreover, any formula that uses the numbers generated by RAND or RANDBETWEEN also recalculates each time.

To change the frequency of recalculations, follow these steps:

  1. Click on the File menu.
  2. Select Spreadsheet Settings.
  3. In the dialog box that appears select the ‘Calculation’ tab.
  4. Click on the dropdown under ‘Recalculation’ and select how often you want the function to recalculate.

Calculation setting in Google sheets

In this tutorial, we showed you how to use two types of functions to generate random numbers in Google Sheets – the RAND and RANDBETWEEN function.

We also explained the basic differences between the two and how you can apply these functions to your spreadsheets. We hope you find our tips and this tutorial helpful.

Other Google Sheets tutorials you may like:

Leave a Comment