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.

RANDARRAY function

The RANDARRAY function returns an array of random numbers

Structure

=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)

Example

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) Image of Randarray dynamic array function

SEQUENCE function

The SEQUENCE function returns a sequence of numbers

Structure

=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

Example

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) Image of Sequence dynamic array function

UNIQUE function

The UNIQUE function finds the unique values from a range of cells

Structure

=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

Example

Here we want to find a unique list of departments from those in column C The formula in J2 is: =UNIQUE(C2:C28) Image of Unique dynamic array function It is thought that the number of dynamic array functions will be expanded and eventually replace the ‘normal’ arrays that are currently available.  

Want to learn more about Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA