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:

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:

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.