COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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.

  1. Using dynamic arrays in Excel
  2. Can I use dynamic arrays?
  3. Introducing dynamic arrays
  4. Filtering and sorting data
  5. Two more functions: RANDARRAY and SEQUENCE (this blog)
  6. 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 RANDARRAY function

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:

RANDARRAY filling one cell

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:

CALC error for rows

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:

Complete RANDARRAY function

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 SEQUENCE function syntax

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:

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:

House price deposits

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:

Descending numbers

This sequence starts at 5, and goes down by 0.25 for each new entry.

This blog has 0 threads Add post