Dynamic array functions are only available in the Microsoft 365 version of Excel. They are very different to many of the other functions as they allow you to work with multiple values at the same time in a formula. The result is not normally one solitary cell but a range of cells called an array.
The functions of SORT, SORTBY and FILTER were covered in an earlier post. In this article RANDARRAY, SEQUENCE and UNIQUE will be looked at in detail.
The RANDARRAY function returns an array of random numbers
=RANDARRAY(rows, columns, min, max, integer) All parts of the function are optional. If none are filled in the result is a single random number between 0 and 1 Rows is the number of rows to be produced Columns is the number of columns to be produced Min is the lowest number Max is the highest number Integer determines whether the result is a decimal (this is the default or if the word FALSE is used) or an integer (if TRUE is entered instead)
In this example we want to create a grid that has 5 rows and 3 columns containing whole numbers between 10 and 20 The formula in cell A1 is: =RANDARRAY(5,3,10,20,TRUE)
The SEQUENCE function returns a sequence of numbers
=SEQUENCE(rows, columns, start, step) Rows is the number of rows to be produced Columns is the number of columns to be produced Start is the start number Step is the amount to increment each subsequent step in the sequence
In this example we want to create a grid that has 5 rows and 3 columns containing a sequence of numbers that start at 10 and go up in 5’s. The formula in cell A1 is: =SEQUENCE(5,3,10,5)
The UNIQUE function finds the unique values from a range of cells
=UNIQUE(array, by col, exactly once) Array is the range of cells to find the unique entries in the row or column By col is optional. It determines whether the unique entries are found by comparing rows or columns of data. FALSE or omitted compares rows, TRUE compares columns Exactly once is optional. If TRUE is used it looks for entries that occur exactly once, FALSE or omitted shows all distinct rows or columns
Here we want to find a unique list of departments from those in column C The formula in J2 is: =UNIQUE(C2:C28)
It is thought that the number of dynamic array functions will be expanded and eventually replace the ‘normal’ arrays that are currently available.