How To Generate A Random Number In Google Sheets

How To Generate A Random Number In Google Sheets
Reading Time: 5 minutes

Quick guide
How to generate a random number in Google Sheets:
↠ A random number between 0 and 1:
=RAND()
↠ A random number between two numbers, say 1 and 10:
=RANDBETWEEN(1,10)
↠ An array of random numbers of height 4 and width 3:
=RANDARRAY(4,3)

Random numbers are useful for a variety of purposes, such as generating data encryption keys, simulating and modelling complex phenomena and for selecting random samples from larger data sets.

In this tutorial, we will learn how to generate random numbers in Google Sheets and how we can use the random number generator functions in Google Sheets for activities such as simulating rolling dice and tossing a coin.

So, let’s get started!

You can make a copy of this spreadsheet and follow along with the tutorial. It contains all the examples and the formulas used.

Generate a random number in Google Sheets using the RAND function

The RAND function generates a random number between 0 and 1 (both inclusive).

The syntax is quite simple and takes no argument.

The syntax is:

=RAND()

Upon entering the function, it will generate a random number between 0 and 1.

Generate a random number in Google Sheets using the RAND function

The RAND function is a volatile function. This means the function will be recalculated and a new result will be generated every time a change is made in the spreadsheet, the page is refreshed, or when the spreadsheet is re-opened.

Generate a random number between two numbers

We can generate a random number between any two numbers in Google Sheets using a couple of methods. Let’s learn how.

Using the RAND function to generate a random number in Google Sheets between two numbers

The RAND function returns a number between 0 and 1 by default. We can however use it to generate a random number between two numbers.

The modified RAND function would be of the form

=RAND()*(b-a)+a

Where a is the lower limit and b is the upper limit

For example, to generate a random number between 1 and 20, enter the following in an empty cell:

=RAND()*19+1 

This will return a random number between 20 and 1.

Generate a random number number between two numbers using the RAND function

Generate a random number in Google  Sheets using the RANDBETWEEN function

As the name implies, the RANDBETWEEN function generates a random number between two given numbers.

The syntax for the RANDBETWEEN function is as follows:

=RANDBETWEEN(low, high)
  • low is the number representing the lower value of the range
  • high is the higher value of the range

Example 1: Simulate rolling a die using the RANDBETWEEN function

Want to play a Ludo game but don’t have a die? You found your luck. You can use Google Sheets to roll an imaginary die. You can enter the RANDBETWEEN function with 1 as the lower number and 6 as the higher number, and it will give you a random number between 1 and 6 (inclusive of both).

Generate a random number between two numbers in Google Sheets usng the RANDBETWEEN function

Example 2: Generate a random number between numbers in cells

We can reference cells with numbers and get a random number between the two numbers. 

Generating a random number between two numbers by referencing cells

Example 3: Bring up Head or Tail by using the RANDBETWEEN function

We can embed the RANDBETWEEN function within the CHOOSE function to return “Head” or “Tail”.

For example, we can keep 1 as the lower number and 2 as the higher number. If the random number generated is 1, the CHOOSE function will return “Head” as the result. If it’s 2, then “Tail” will be returned.

The syntax is:

=CHOOSE(RANDBETWEEN(1,2),”Head”,”Tail”)
Using the CHOOSE and the RANDBETWEEN functions to bring up "Head" or "Tail"

Like the RAND function, the RANDBETWEEN function is also volatile. And so the results change every time the spreadsheet is modified.

Generate a random number between two numbers in Google Sheets using the RANDARRAY function

We can also use the RANDARRAY function to generate a random number in Google Sheets by keeping the array range empty and appending the numbers in the form *(b-a)+a, like it is with the RAND function.

To generate a random number between 1 and 100 would be as follows:

=RANDARRAY()*99+1
Random number between two numbers using the RANDARRAY function

Generate an array of random numbers

We can use the RANDARRAY function to generate an array of random numbers in Google Sheets.

The syntax of the RANDARRAY function is as follows:

= RANDARRAY(rows, column)
  • Rows is the number of rows in the returned array
  • Columns is the number of columns in the returned array. 

If you omit the row and column index from the RANDARRAY function, it will default to 1. RANDARRAY is a volatile function, and it will recalculate and return a new array of values any time the spreadsheet is changed. The values returned will follow a uniform distribution over 0 and 1, where any single value is equally likely to occur.

Tip: To save the random numbers as fixed numbers for reference, copy the numbers and paste them as values. Copy→Paste special→Values only (or use Ctrl+Shift+V).

To generate an array of random numbers with the array size 5×3, we’d use the following formula:

=RANDARRAY(5,3)

Where, 5 is the height of the array and 3 is the width.

Generating an array of random numbers in Google Sheets

Note that the RANDARRAY function generates random numbers between 0 and 1. So if you want to generate an array of whole numbers, you can use the ROUND function with the RANDBETWEEN function.

Generate an array of random whole numbers in Google Sheets

To generate an array of random whole numbers in Google Sheets, we can nest the RANDBETWEEN function inside the ROUND function, and simply drag the formula vertically and horizontally to the desired number of rows and columns.

For example, to generate an array of random whole numbers between 1 and 100, we’d use the following formula:

=ROUND(RANDBETWEEN(1,100))

This will return a random whole number. Dragging the formula 4 rows down and 3 columns to the right gives us the following result.

An array of random whole numbers

CONCLUSION

It is very easy to generate a random number in Google Sheets. There are a few different ways we can generate a random number in Google Sheets as we have learned. And there are several ways you can use the functions to generate a random number in Google Sheets–namely, the RAND, the RANDBETWEEN, and the RANDARRAY.

See also

Generating random numbers is just one of the many things you can use Google Sheets for. We have a number of tutorials that cover tricks and tips in Google Sheets. You can discover them here.

Here are some related articles you may be interested in:

https://blog.tryamigo.com/arrayformula-in-google-sheets/

https://blog.tryamigo.com/how-to-use-the-char-function-in-google-sheets/

https://blog.tryamigo.com/how-to-fix-formula-parse-errors-in-google-sheets/