Have you ever needed to quickly generate a list of numbers? Maybe you need to create a list from 1-100. Maybe you want to create a list of the first 100 even numbers. Maybe you want to create those same lists in descending order. Did you know that Excel has a function called SEQUENCE which allows you to quickly create these lists?
The SEQUENCE function is used to generate an array of sequential numbers. The syntax of the function is:
=SEQUENCE (rows, [columns], [start], [step])
- Rows (required)
- This is the number of rows to be filled
- Columns (optional)
- This is the number of columns to be filled
- If skipped, this defaults to 1
- Start (optional)
- This is the starting number in the sequence
- If skipped, this defaults to 1
- Step (optional)
- This is the increment for each subsequent value
- If skipped, this defaults to 1
- This can be negative to provide a descending list
The following example shows a single column list from 1-10
The same formula doubles the result when the column parameter is defined. Note that the results read from right to left (e.g., the entire row) and then goes down.
Here is the same formula when adding both a starting value and an increment.
Not only can this formula be used to quickly generate lists, but it can also be used as part of a combination:
- With the DATE formula to define a calendar
- With the TIME formula to create a daily schedule
- With data from another column to create unique IDs for items like products or employees
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Creating Control Objects from the Modeling Workbench
IBM Planning Analytics Tips & Tricks: Shortcuts in PAW Models
IBM Planning Analytics Tips & Tricks: Customizing Background Colors for Data and Header Cells
