SEQUENCE function

SEQUENCE(Size, FirstValue?, StepValue?) SEQUENCE(Size; FirstValue?; StepValue?)

Size

Number

The number of values in the returned array.

FirstValue

Number (optional)

The first value of the array.

StepValue

Number (optional)

The amount each subsequent value in the array is incremented by.

Returns

{ Number }

An array containing a sequence of numbers with the given characteristics.

Returns an array containing a sequence of numbers. SEQUENCE(10)SEQUENCE(10) returns { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }{ 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }.

SEQUENCE is useful when supplying array parameters to other functions. For instance, ROMAN(5)ROMAN(5) returns "V", while these equivalent formulas both return { "I", "II", "III", "IV", "V" }{ "I"; "II"; "III"; "IV"; "V" }:

ROMAN({ 1, 2, 3, 4, 5 })ROMAN({ 1; 2; 3; 4; 5 })

ROMAN(SEQUENCE(5))ROMAN(SEQUENCE(5))

If you need the first 1,000 roman numerals, writing ROMAN(SEQUENCE(1000))ROMAN(SEQUENCE(1000)) is probably preferable to writing the equivalent formula ROMAN({ 1, 2, ..., 1000})ROMAN({ 1; 2; ..., 1000}).

Sequences can start at other values than 1 by supplying a second parameter. This formula returns a sequence of five values, starting at 100, { 100, 101, 102, 103, 104 }{ 100; 101; 102; 103; 104 }:

SEQUENCE(5, 100)SEQUENCE(5; 100)

This formula returns 300 dates, starting with today's date:

SEQUENCE(300, TODAY())SEQUENCE(300; TODAY())

To set the amount to increment each subsequent value in the sequence, which is one by default, supply a third parameter. This formula returns { 100, 300, 500, 700, 900 }{ 100; 300; 500; 700; 900 }:

SEQUENCE(5, 100, 200)SEQUENCE(5; 100; 200)

This formula returns five times, starting with the current time, each value in the array incremented by one hour (1/24th of a day):

SEQUENCE(5, NOW(), 1/24)SEQUENCE(5; NOW(); 1/24)

Examples

SEQUENCE(10)SEQUENCE(10)

Returns a number sequence starting at 1: { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }{ 1; 2; 3; 4; 5; 6; 7; 8; 9; 10 }.

SEQUENCE(5, 100)SEQUENCE(5; 100)

Returns a number sequence starting at 100: { 100, 101, 102, 103, 104 }{ 100; 101; 102; 103; 104 }

SEQUENCE(5, 100, 200)SEQUENCE(5; 100; 200)

Returns a number sequence starting at 100, where each subsequent value of the sequence is incremented by 200: { 100, 300, 500, 700, 900 }{ 100; 300; 500; 700; 900 }.

ROMAN(SEQUENCE(5))ROMAN(SEQUENCE(5))

Returns the array { "I", "II", "III", "IV", "V" }{ "I"; "II"; "III"; "IV"; "V" }. SEQUENCE(5)SEQUENCE(5) returns { 1, 2, 3, 4, 5 }{ 1; 2; 3; 4; 5 }, which causes ROMAN to return an array of roman numerals corresponding to the input array.

SEQUENCE(300, TODAY())SEQUENCE(300; TODAY())

Returns 300 dates, starting with today's date.

SEQUENCE(5, NOW(), 1/24)SEQUENCE(5; NOW(); 1/24)

Returns five times, starting with the current time, each value in the array incremented by one hour (1/24th of a day).

CHAR(SEQUENCE(26, 97))CHAR(SEQUENCE(26; 97))

Returns an array of the lower-case letters of the English alphabet. SEQUENCE is instructed to produce character codes for the 26 letters of the alphabet, where a lower-case "a" starts at 97. CHAR then produces one letter for every character code.

TEXTJOIN("", FALSE, CHAR(SEQUENCE(26, 97)))TEXTJOIN(""; FALSE; CHAR(SEQUENCE(26; 97)))

Returns a string containing the lower-case letters of the English alphabet. SEQUENCE is instructed to produce character codes for the 26 letters of the alphabet, where a lower-case "a" starts at 97. CHAR then produces one letter for every character code. TEXTJOIN joins together all the array elements into a single text string, using the empty text string as a separator.

DEC2HEX(SEQUENCE(100))DEC2HEX(SEQUENCE(100))

Returns 100 integers, starting with 1, formatted as hexadecimal (base 16) numbers. (1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, 10, 11, etc).

DATE(YEAR(TODAY()), SEQUENCE(12), 1)DATE(YEAR(TODAY()); SEQUENCE(12); 1)

Returns the starting dates of the months of the current year.

EOMONTH(DATE(YEAR(TODAY()), SEQUENCE(12), 1), 0)EOMONTH(DATE(YEAR(TODAY()); SEQUENCE(12); 1); 0)

Returns the end dates of the months of the current year.

Learn

Blog

Pricing