Return an array of random decimal or integer numbers between specified values
=RANDARRAY([rows],[columns],[min],[max],[whole_number])
Only available in Excel for Microsoft 365.
RANDARRAY is useful for generating a single or array of random decimal or integer numbers that recalculate every time a sheet is opened or changed or F9 is pressed.
RANDARRAY defines the height ('rows') and width ('columns') of the array, the smallest ('min') and largest values ('max') and whether to return decimals ('FALSE') or integers ('TRUE').
RANDARRAY and decimal or whole numbers.
In the examples below, the RANDARRAY function is used to generate volatile and non-volatile arrays of random decimal and integer values.
Use the RANDARRAY function to return an array of random decimal or integer numbers between specified values
Syntax
=RANDARRAY([rows],[columns],[min],[max],[whole_number])
The RANDBETWEEN function has 5 optional arguments:
- rows. Optional. The height in rows of the array to return.
- columns. Optional. The width in columns of the array to return.
- min. Optional. The smallest / minimum value to be returned.
- max. Optional. The largest / maximum value to be returned.
- whole_number. Optional. Return an integer with TRUE and a decimal value with FALSE.
Try it now!
- Enter your formulas into the dark grey cells.
Scope
RANDARRAY defines the height ('rows') and width ('columns') of the array, the smallest ('min') and largest values ('max') and whether to return decimals ('FALSE') or integers ('TRUE').
|
=RANDARRAY(2,3) || result is an array of 2 rows and 3 columns with random decimal values between 0 and 1. |
|
=RANDARRAY(2,3,1,9999) || result is an array of 2 rows and 3 columns with random decimal values between 1 and 9999. |
|
=RANDARRAY(2,3,1,9999,TRUE) || result is an array of 2 rows and 3 columns with random whole numbers / integers between 1 and 9999. |
|
=RANDARRAY(2,3,1,9999,TRUE) then press F9 || result is an array of 2 rows and 3 columns with random integers between 1 and 9999 that do not change when the workbook is opened or changed. |
Caveats
- RANDARRAY is only available to Microsoft 365 subscribers.
- RANDARRAY is a volatile function that will change every time a workbook is opened or changed or the F9 key is pressed.
- =RANDARRAY() with no arguments will return a single value between 0 and 1.
- =RANDARRAY(rows,columns) without a min or max argument will return an array of values between 0 and 1 by default.
- =RANDARRAY(rows,columns,min,max) without a whole_number argument will return an array of decimal values (FALSE) by default.
- RANDARRAY returns an array that may spill into other cells already containing values. This will return the #SPILL! error.
- RANDARRAY can return a single cell, a row of values, a column of values or an array of values.
- =RANDARRAY(1,1) || result is a single cell (1 row, 1 column).
- =RANDARRAY(3,1) || result is a column (3 rows, 1 column).
- =RANDARRAY(1,3) || result is a row (1 row, 3 columns).
- =RANDARRAY(3,3) || result is an array (3 rows, 3 columns).
- Dynamic arrays only work between workbooks if both workbooks are open. If one of the workbooks is closed, Excel will return the #REF! error.
- To create an array of non-volatile random numbers, enter the =RANDARRAY([rows],[columns],[min],[max],[whole_number]) function into the formula bar then press F9 to convert the formula into its result. Alternatively, copy and paste special values the result into the desired cell.
- =RANDARRAY(2,3,1,9999,TRUE) followed by F9 || result is an array of non-volatile numbers presented, for example, as ={787,6653,3861;6791,7460,9008}.
Common Issues
- #VALUE!: the min number argument is greater than the max number argument.
- #REF!: one of the workbooks referenced in the dynamic array is closed.
- #SPILL!: the array is spilling into cells containing other values.
Related Functions
- RAND: return a random decimal value between 0 and 1.
- RANDBETWEEN: to generate random integers between 2 values.
Functions Category