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

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

### 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) 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.