RANDARRAY Function Examples – Excel & Google Sheets
This tutorial demonstrates how to use the RANDARRAY Function in Excel and Google Sheets to generate an array of random numbers.
RANDARRAY Function Overview
The RANDARRAY Function is used to return an array of random numbers. It was introduced as a replacement for the RAND Function and the RANDBETWEEN Function.
The RANDARRAY Function simplifies the process of generating random numbers by:
- Allowing the user to input the formula in only one cell.
- Allowing the user to specify the number of random values to be generated.
To use the RANDARRAY Excel Worksheet Function, select a cell and type:
RANDARRAY Function Syntax and inputs:
=RANDARRAY([rows], [columns], [min], [max], [integer])[rows] (optional) – The number of rows to be returned and when left blank it defaults to 1.
[columns] (optional) – The number of columns to be returned and when left blank it defaults to 1.
[min] (optional) – The minimum number you would like returned and if left blank it defaults to 0. When a value is specified for this input it should be less than the value provided for the [max] argument.
[max] (optional) – The maximum number you would like returned and if left blank it defaults to 1. When a value is specified for this input it should be greater than the value provided for the [min] argument. You should note that when both the [min] and [max] functions are left blank the RANDARRAY Function returns an array of numbers between 0 and 1.
[integer] (optional) – Return a whole number or a decimal value. You can enter either TRUE to return a whole number or FALSE to return a decimal number. When this argument is left blank it defaults to FALSE therefore the formula returns a decimal number.
How to use the RANDARRAY Function
Return Multiple Rows and Columns
We can change the size of the array that the RANDARRAY Function returns by changing the number of rows and columns. To do this we specify the [rows] and [columns] arguments.
To return an array of random numbers that is 5 Rows tall and 1 Column wide, we enter the following formula in cell A2:
=RANDARRAY(5, 1, 1, 10, TRUE)
To return an array of random numbers that is 5 Rows tall and 3 Columns wide, we enter the following formula in A2:
=RANDARRAY(5, 3, 1, 10, TRUE)
Return Numbers Between Two Values
When we want to return numbers between two values we specify the values in the [min] and [max] arguments.
To return a list of random numbers between 1 and 10, we enter the following formula in A2:
=RANDARRAY(5, 1, 1, 10)
Return Decimal Numbers or Whole Numbers
To return 5 decimal numbers between 1 and 10, we enter the following formula in A2:
=RANDARRAY(5, 1, 1, 10, FALSE)
Note that we’ve set the [integer] argument to FALSE which means the output will be decimal numbers.
The formula can also be written as:
=RANDARRAY(5, 1, 1, 10)When the [integer] argument is not specified it defaults to FALSE:
To return 5 whole numbers between 1 and 10, we enter the following formula in A2:
=RANDARRAY(5, 1, 1, 10, TRUE)
Note that we’ve set the [integer] argument to TRUE which means the output will be whole numbers
RANDARRAY Errors
#VALUE!
This error occurs when the value in the [min] argument is greater than the value in the [max] argument.
To correct this error, set the value in the [min] argument to a value that is less than that in the [max] argument.
#SPILL!
This error occurs when there is a value in the Spill Range i.e. the range where the RANDARRAY Function places its results.
To correct this error, clear the range that Excel highlights.
RANDARRAY Tips & Tricks
- Since all the arguments in the RANDARRAY Function are optional, entering the formula below in any cell returns a single value between 0 and 1:
=RANDARRAY()
- If the [min] and [max] arguments are not specified, RANDARRAY will default to 0 and 1 respectively and return an array of random numbers between 0 and 1.
=RANDARRAY(5, 1, , , FALSE)
- If the [integer] argument is not specified, RANDARRY will default to FALSE and return an array of decimal numbers:
=RANDARRAY(5, 1, 1, 10 ) - RANDARRAY is different from the RAND and RANDBETWEEN functions. While RAND and RANDBETWEEN return a single value, RANDARRAY returns an array.
- Ensure that the cells below the input cell are blank to avoid the Spill Error.
RANDARRAY in Google Sheets
The RANDARRAY Function works exactly the same in Google Sheets as in Excel:
