Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.