560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Find out how dynamic arrays in Excel will change your (working) life!
Part five of a six-part series of blogs
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.
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.
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 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.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street