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)
Sample spreadsheet with examples here
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!
- Generate a random number
- Generate a random number between two numbers
- RANDBETWEEN function
- Dice simulation in Google Sheets
- Random numbers between numbers in cells
- Toss a coin to get head or tail
- RANDARRAY function
- Array of random numbers
- Array of random whole numbers
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.
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)+aWhere 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 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).
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.
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”)
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
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.
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.
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/