This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

Microsoft Excel training

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Two more functions: RANDARRAY and SEQUENCE

Using RANDARRAY to generate random numbers

The SEQUENCE function

Head office

London

Manchester