On 24 September 2018, Microsoft announced some new functions for Excel. These new functions make use of changes made to Excel’s calculation engine, which enable a single formula to return (or “Spill” using the correct terminology) results into multiple cells. RANDARRAY is one of these new functions.
The regular RAND function calculates a single random number which is greater than or equal to zero and less than 1. The RANDARRAY function calculates a random number in the same way, but the user can specify how many rows and columns of random numbers to return.
At the time of writing, Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel. The updates will be rolled out to all Office 365 subscribers at a future point, but first Microsoft need to assess the impact the changes to the calculation engine will have. The new dynamic array functions and features are not available in Excel 2019 or previous versions, so it is definitely worth getting an Office 365 subscription, otherwise, you will be waiting until Excel 2022, and that’s just too long to wait!
Arguments of the RANDARRAY function
RANDARRAY has five arguments:
=RANDARRAY(Rows, Columns, Min, Max, Integer)
- Rows: The number of rows of random numbers to return
- [Columns]: The number of columns of random numbers to return. If this argument is excluded, the default is 1.
- [Min]: The lowest number to be returned. If excluded the default is 0.
- [Max]: The highest value to be returned. If excluded the default value is 0.999999999999999 (i.e. just less than 1)
- [Integer]: TRUE = return whole numbers only, FALSE = return decimal values to 15 decimal places.
One attribute to be aware of is that RANDARRAY is a volatile function. This means that it will recalculate every time any cell changes. If you’ve used the RAND function before, this functionality will not be new to you.
Examples of using the RANDARRAY function
The following examples illustrate how to use the RANDARRAY function.
Example 1 – Basic usage
In the screenshot below, we see a basic example of using the RANDARRAY function.
The formula in Cell B2 is:
The formula above creates an array of random numbers which is four rows and three columns in size.
Example 2 – Using RANDARRAY with SORTBY
This example shows how to turn a sorted list into a random order.
The formula in Cell D3 is:
RANDARRAY(8) is creating a list of 8 random numbers SORTBY is then putting Cells B3-B10 into the order of the random numbers.
Example 3 – Using all the arguments of RANDARRAY
In the screenshot below, we can see all the arguments of the RANDARRAY functions.
The formula in Cell B2 is:
The formula will create an array of results which is four rows, three columns, minimum of 50, maximum of 150, with only whole numbers.
Want to learn more?
There is a lot to learn about dynamic arrays and the new array functions. Check out my other posts here to learn more:
- Introduction to dynamic arrays – learn how the excel calculation engine has changed.
- UNIQUE – to list the unique values in a range
- SORT – to sort the values in a range
- SORTBY – to sort values based on the order of other values
- FILTER – to return only the values which meet specific criteria
- SEQUENCE – to return a sequence of numbers
- RANDARRAY – to return an array of random numbers
Also, here are some other resources you might find useful:
- Ebook – Dynamic arrays straight to the point – By Bill Jelen (free until December 2018)
- Video – Comprehensive dynamic array formulas: The power of dynamic arrays
- Blog Post – Dynamic array formulas & spill ranges