EXCEL function
SEQUENCE
for Excel 2007 to 2019
Download the Excel file with the VBA code of the functions
SORT, SORTBY, FILTER, XLOOKUP, XMATCH, UNIQUE, SEQUENCE, RANDARRAY
and all examples shown on these web pages:
▸Download Excel file
(Version 3.7 - Last update: 2023-01-06 - XLookup and XMatch now fit for use in array formulas ▸more on this)
What is it about?
In Excel 365 and from Excel 2021, Microsoft implemented the long-missing cell functions SORT, SORTBY, FILTER, XLOOKUP and others. However, they were not retrofitted for older versions of Excel, so that all users from Excel 2007 up to and including Excel 2019 had to do without them.
There is now a solution for users of older versions of Excel (since the end of January 2022).
With the help of VBA so-called UDFs (user defined functions) were implemented that have exactly the same parameter lists as the corresponding Microsoft functions.
In cases where the functions provided here return multiple values, their input must be terminated with CTRL-SHIFT-ENTER. Before entering the formula, an area must be selected on the spreadsheet that contains the returned data - as is usual with the so-called CSE array formulas (CSE = Control Shift Enter).
▸This tutorial provides a basic and detailed introduction to array formulas.
With Function names of UDFs it doesn't matter whether the letters are written in upper or lower case.
For instructions on integrating the functions in your own Excel file, see ▸Integrating the functions.
SEQUENCE function
The SEQUENCE function allows you to create a sequence of numbers. You can freely choose the size of the target area as well as the start value and the increment.
Syntax
= Sequence ( [rows], [columns], [start], [step] )
| Parameter | Explanation |
|---|---|
| rows (optional) |
Number of rows in the result array (default: 1) |
| columns (optional) |
Number of columns in the result array (default: 1) |
| start (optional) |
The first number in the sequence (default: 1) |
| step (optional) |
The amount to increment each subsequent value in the sequence (default: 1) |
The Sequence function has the same behavior as the Excel 365 function SEQUENCE.
See also the function description from Microsoft:
▸https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90
Sequence - Example 1
A 12 ✕ 5 array of integers is generated. The starting value is 100, the increment is 25. Before entering this formula, it makes sense to also select an area of 10 rows and 5 columns. The selected target area is filled with the number sequence line by line.
The formula for this is:
= Sequence (12, 5, 100, 25)
Some might wonder why this function is useful at all.
Example 2 shows that the Sequence (SEQUENCE) function can be of great use.
Sequence - Example 2
A specific event takes place every 14 days on Wednesdays throughout the year.
A simple formula using the Sequence function lists the date of all appointments in 2022:
= Sequence (27, 1, $C$4, 14)
Enter the first date of the year in cell C4. All appointments for the year 2022 then appear in the C7:C33 range.
Since a date without time in Excel represents an integer that indicates the number of days since 1/1/1900, it is sufficient to take the integer of the date 01/05/2022 as the start value and the number 14 as the increment. The cells get a date format - done.
Depending on the day of the week or the year, a maximum of 27 appointments can occur. Therefore, it makes sense to set the target range to 27 rows and 1 column. However, it can happen that the last date is already in the new year. This can be caught with an enclosing IF function - as was done in the range F7:F33.
In the F7:F33 area, all fortnightly appointments are also listed, but the year and day of the week can be freely selected using two drop-down fields.
The formula used is:
= IF (YEAR (Sequence (27, 1, DATE ($F$4, 1, 1) - MOD (DATE ($F$4, 1, 1), 7) + MOD (MATCH ($F$5, $A$7:$A$13, 0), 7), 14)) = $F$4, Sequence (27, 1, DATE ($F$4, 1, 1) - MOD (DATE ($F$4, 1, 1), 7) + MOD (MATCH ($F$5, $A$7:$A$13, 0), 7), 14), "")
This formula needs a list of days of the week in the range A7:A13.
Sharing knowledge is the future of mankind