Generating a Range of Sequential Numbers

August 22, 2022 - by Bill Jelen

I have a cool trick in my Power Excel seminars where I show how you can Ctrl + drag the fill handle from a cell with the number 1, and Excel will extend to 2, 3, 4. It is an obscure trick, and not many people know it. When I first saw the SEQUENCE function, I thought it might be for people who had never discovered this trick. But as you will see here, the SEQUENCE function is the key to making many other Excel functions into array functions.

SEQUENCE generates an array of numbers. The syntax is =SEQUENCE(rows, [columns], [start], [step]).
While =SEQUENCE(10) generates 1 through 10, you can customize the numbers with =SEQUENCE(10,3,5,10) to generate a 10-row by 3-column array starting at 5 and stepping by 10.

The syntax of SEQUENCE is Rows, Columns, Start, Step. In this figure =SEQUENCE(10,3,5,10) returns three columns and 10 rows of numbers. The first row is 5, 15, 25. The next row is 35, 45, and 55. This pattern continues down to 275, 287, 295 in the tenth row. — Figure 629. Generating a sequence of numbers.

The RANDARRAY function is great for driving Monte Carlo analysis. To use it, you just specify how many rows and columns of random numbers you want.

The syntax for RANDARRAY is Rows, Columns, Min, Max, Integer. Every argument is optional. In this image, =RANDARRAY(10,3) returns 10 rows and 3 columns of RAND. — Figure 630. Finding 30 random numbers.

A March 2019 improvement to RANDARRAY allows you to specify a min and max and also that you only want integers.

This formula shows how RANDARRAY can now return integers between a Min and a Max. Five columns of integers between 11 & 17 happen with =RANDARRAY(,5,11,17,True). — Figure 631. By leaving the first argument out, you get 1 row by default.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jeffrey Brandjes on Unsplash