BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.
- Using dynamic arrays in Excel
- Can I use dynamic arrays?
- Introducing dynamic arrays
- Filtering and sorting data
- Two more functions: RANDARRAY and SEQUENCE (this blog)
- Implications of dynamic arrays
Posted by Andy Brown on 14 February 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Two more functions: RANDARRAY and SEQUENCE
To go with dynamic arrays, there are two new functions in Excel. You can use RANDARRAY to fill a block of cells with random numbers, and SEQUENCE to fill a block of cells with a sequence of numbers.
Using RANDARRAY to generate random numbers
The syntax of this function is as follows:
The function takes 5 arguments, although all of them are optional.
The arguments are as follows:
| Argument | What it denotes | Default value |
|---|---|---|
| rows | The number of rows in the spill range created | 1 |
| columns | The number of columns in the spill range created | 1 |
| min | The lower boundary for the random numbers generated | 0 |
| max | The upper boundary for the random numbers generated | 1 |
| integer | Whether to use decimal random numbers (false) or integers (true). | false |
So at its most basic, the function would give you a single random number between 0 and 1:
The most basic use of the RANDARRAY function, giving a single number.
If you don't specify a valid number of rows or columns, you'll get one of the new #CALC! errors:
The number of rows and columns hasn't been filled in, so Excel can't fill an array.
Here's the same formula, but with 3 rows and 2 columns:
This formula is returning a 3 x 2 block of random integers between 1 and 10.
The SEQUENCE function
The SEQUENCE function generates a series of (usually) ascending numbers:
The function fills a block of cells with a sequence of numbers.
The arguments to the function are as follows:
| Argument | What it denotes |
|---|---|
| rows | The number of rows to fill |
| columns | The number of columns to fill |
| start | Optionally, the number to start at (the default is 1) |
| step | Optionally, the number to add on each time (the default is 1) |
Here's an example of a simple sequence:
This creates a sequence of integers filling 1 column and 3 rows.
Here's an example which generates a number of possible house price deposits for a model:
This allows you to try out deposits from 0 up to 20000 pounds.
Finally, this example shows that sequences can go down as well as up:
This sequence starts at 5, and goes down by 0.25 for each new entry.
- Using dynamic arrays in Excel
- Can I use dynamic arrays?
- Introducing dynamic arrays
- Filtering and sorting data
- Two more functions: RANDARRAY and SEQUENCE (this blog)
- Implications of dynamic arrays