What Function is Used to Define and Continue Sequence of 2 6 12 20 30
In Microsoft Excel, the SEQUENCE function is used to generate a sequence of numeric values in different formats. In this article, you'll learn how you can use this SEQUENCE function efficiently with different criteria and by combining it with other functions in Excel.
The above screenshot is an overview of the article which represents an application of the SEQUENCE function in Excel. You'll learn more about the dataset as well as the methods to use the SEQUENCE function properly in the following sections of this article.
Download Practice Workbook
You can download the Excel workbook that we have used to prepare this article.
Introduction to the SEQUENCE Function
- Function Objective:
The SEQUENCE function is used to create a sequence of numeric values.
- Syntax:
=SEQUENCE(rows, [columns], [start], [step])
- Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| rows | Required | The number of rows. |
| [columns] | Optional | The number of columns. |
| [start] | Optional | Start number in the return array. |
| [step] | Optional | The common difference between successive two values in the sequence of numbers. |
- Return Parameter:
An array containing a sequence of numbers with the defined specifications.
16 Examples of Using SEQUENCE Function in Excel
1. Basic Use of SEQUENCE Function with Only One Argument
The first argument of the SEQUENCE function is 'rows' which indicates the number of rows to be shown in the spreadsheet. If you don't input any other arguments then the function will return the specified number of rows where the first row will contain the number '1' and later all other sequential numbers will be displayed in the following rows.
So, in the picture below, Cell B4 contains the formula:
Read More: 51 Mostly Used Math and Trig Functions in Excel
2. SEQUENCE Function with Two Arguments in Excel
Since the second argument of the function denotes the number of columns, the combination of the first two arguments will result in an array of the specified rows and columns.
In Cell B4, the SEQUENCE function with rows and columns arguments will be:
Read More: 44 Mathematical Functions in Excel (Download Free PDF)
3. SEQUENCE Function with Three Arguments in Excel
Now the third argument of the function is [start] which denotes the starting value or number to be shown in the first cell of the first row in an array.
So, with the first three arguments, the function will return the array as shown in the following screenshot. And the formula in Cell B4 is:
Where the starting value is 10 in the array that has been defined in the third argument of the function.
4. SEQUENCE Function with Four Arguments in Excel
The fourth argument of the function [step] denotes the interval that has to be followed between any two successive values in the array. Assuming that we want to build an arithmetic series of integer numbers starting from 10 where the common difference is 5.
The required formula in Cell B4 will be:
5. Use of SEQUENCE Function to Generate Dates or Months in Excel
i. Creating Sequential Dates with SEQUENCE and TODAY Functions
The SEQUENCE function is very useful for making a number of successive dates starting from a fixed date. By using the TODAY function inside as the third argument, we can define the current date which will be the start date. Assuming we are going to create a list of ten successive dates starting from the current date.
The related formula in Cell B5 should be:
=SEQUENCE(10,1,TODAY(),1)
ii. Creating a List of First Dates for Sequential Months with EDATE and SEQUENCE Functions
EDATE function is used to display the date after or before a specified number of months from a particular date. By combining EDATE, DATE, and SEQUENCE functions together, we can easily create a list of all first dates for several successive months. Let's say we want to show the first dates of all months in the year 2021.
So, in the output Cell B4 in the following picture, the required formula will be:
=EDATE(DATE(2021,1,1),SEQUENCE(12,1,0))
iii. Making a List of 12-Month Names with SEQUENCE Function in Excel
By using the TEXT function around the SEQUENCE function, we can also prepare a list of successive twelve months in a year.
The required formula in Cell B5 should be:
=TEXT(EDATE(DATE(2022,1,1),SEQUENCE(12,1,0)),"mmmm")
6. Combination of SEQUENCE and TRANSPOSE Functions in Excel
By applying the SEQUENCE function with all four arguments inside, we can create an array of some sequential numbers and the flow of the numbers will be from left to right like in the picture below.
Let's assume, we want to display the sequence of these numbers from top to bottom in the array. In this case, we have to use the TRANSPOSE function which will convert the rows into columns and the columns into rows.
So, the required formula in the output Cell B10 should be:
=TRANSPOSE(SEQUENCE(5,3,10,5))
7. Creating a Sequence of Roman Numbers in Excel
Creating a list of successive Roman numbers is also possible with the help of ROMAN and SEQUENCE functions.
The required formula in any cell should be:
=ROMAN(SEQUENCE(5,3,1,1))
This will create the fifteen successive Roman numbers starting from 'i' in the array of five rows and three columns.
8. Use of SEQUENCE with INDEX Function in Excel
Now let's have a look at the dataset below. Each smartphone brand and its model appear twice in the table: one is with the actual price and another is with a discounted price. Let's assume, we want to show the rows of all brands containing discounted prices only.
In the output Cell B18, the related formula will be:
=INDEX(B6:D15,SEQUENCE(COUNTA(B6:B15)/2,1,2,2),SEQUENCE(1,3))
After pressing Enter, you'll get the resultant array with all smartphone brands and model names with their discounted prices only.
🔎 How Does the Formula Work?
➯ COUNTA function counts the total number of cells in the range of B6:B15. Then the output (10) is divided by 2 and the resultant value is inputted as the first argument (rows) of the SEQUENCE function.
➯ In the second argument (row_num) of the INDEX function, the SEQUENCE function defines which rows have to be extracted from the table.
➯ Finally, in the third argument of the INDEX function, another SEQUENCE function defines all the columns that have to be considered for extracting data.
Similar Readings
- How to use RAND function in Excel (5 Examples)
- LARGE function in Excel
- How to Use SUMPRODUCT Function in Excel (4 Examples)
- Solving equations in Excel (polynomial, cubic, quadratic, & linear)
- How to Use SUMIF Function in Excel (With 5 Easy Examples)
9. Creating a Random Order for SEQUENCE Outputs
What we have learned so far about the SEQUENCE function is it returns with a specified number of sequential values. But we can also randomize the sequence or the order of the numbers in the resultant array. To do this, we have to use the SORTBY function outside the SEQUENCE function and the sorting will be performed based on the RANDARRAY function where RANDARRAY function returns random numbers with no particular order or sequence.
In Cell B4, the related formula to create a random order for sequential numbers should be:
=SORTBY(SEQUENCE(10),RANDARRAY(10))
10. Creating a Dynamic Calendar with SEQUENCE Function in Excel
One of the most comprehensive uses of the SEQUENCE functions is preparing a calendar month. Let's assume, we have a random date value in Cell C4 and that is 01-08-2021 or 1 August 2021. By incorporating the SEQUENCE and the WEEKDAY functions together, we can extract the month from that specified date and thereby show all the calendar days for that particular month.
The required formula to display a calendar month based on a date in Cell B7 will be:
=SEQUENCE(6,7,C4-WEEKDAY(C4)+1)
🔎 How Does the Formula Work?
➯ In the SEQUENCE function, the number of rows has been defined by 6 and the number of columns by 7.
➯ The start date has been defined by "C4-WEEKDAY(C4)+1". Here the WEEKDAY function extracts the serial number of the weekday (By default, 1 for Sunday and thus successively 7 for Saturday). The date in Cell C4 subtracts the number of weekdays and later by adding '1', the start date becomes the first date of the prospective month.
➯ The SEQUENCE function then shows the successive dates from left to right in an array of 6 rows and 7 columns. Don't forget to customize the format of the dates to show only the serial of the days.
11. Making a Repeated Sequence with the Help of MOD and SEQUENCE Functions
By using MOD and SEQUENCE functions together, we can show a particular sequence of numbers or values multiple times in a column, a row or, an array. In the following screenshot, integer values from 1 to 4 have been displayed multiple times in a column.
The required formula in Cell B4 to create this array is:
🔎 How Does the Formula Work?
➯ Since here the integer values from 1 to 4 are to be shown multiple times, the multiple of 4 has to be assigned as the number of rows in the SEQUENCE function.
➯ "SEQUENCE(12)-1", this part of the formula returns the following array:
{0;1;2;3;4;5;6;7;8;9;10;11}
➯ MOD function divides each of the integer values in the array with 4 and returns all the remainders in a final array.
12. Creating Repeated Numbers in a Sequence in Excel
The combination of ROUNDUP, as well as the SEQUENCE function, paves the way to create the repeated numbers in a sequence. In the picture below, integer values from 1 to 5 have been shown twice in succession in Column B.
The required formula that has been used to create the return array is:
=ROUNDUP(SEQUENCE(10, 1, 1/2, 1/2), 0)
🔎 How Does the Formula Work?
➯ Here the start point and the step value in the SEQUENCE function have been assigned with ½ in both cases.
➯ With the mentioned arguments, the SEQUENCE function would return the following array:
{0.5;1;1.5;2;2.5;3;3.5;4;4.5;5}
➯ Finally, the ROUNDUP function rounds up all the decimals to the next integer digit.
Read More: How to Use ROUNDDOWN Function in Excel (5 Methods)
13. Generating Empty Values in a Sequence of Numbers
If you need to leave an empty cell or a space after each value in a sequence of numbers, then you can merge IF, INT, and SEQUENCE functions as well to get the output. In the following picture, the numbers from 1 to 5 have been shown in a sequence with a space after each value in the sequence.
The required formula in Cell B4 is:
=IF(INT(SEQUENCE(10, 1, 1, 1/2))=SEQUENCE(10, 1, 1, 1/2), SEQUENCE(10, 1, 1, 1/2), "")
🔎 How Does the Formula Work?
➯ SEQUENCE(10,1,1,½), this repeated part of the formula returns the following array:
{1;1.5;2;2.5;3;3.5;4;4.5;5;5.5}
➯ INT(SEQUENCE(10,1,1,½)) returns another array of:
{1;1;2;2;3;3;4;4;5;5}
➯ With the use of the IF function, the formula checks if the values in the second array match with the values in the first one. If the values are matched, the matched rows return with perspective values. Otherwise, the rows remain empty which are considered blank cells in the output column.
14. Formatting a Sequence of Numbers with Leading Zeros in Excel
If you know how the TEXT function works then formatting a sequence of numbers with leading zeros is too easy. For example, all the numbers in a random sequence will have 4 digits and those of which don't have 4 digits will contain leading zeros.
The related formula in Cell B4 will be:
=TEXT(SEQUENCE(10,1,5,50),"0000")
15. Creating a Reverse Order in a List with SEQUENCE Function
Now let's assume that we want to reverse the order of the cells in a list. To execute the purpose, we can use the INDEX and ROWS functions here along with the SEQUENCE function.
In Column B, there are some random names and in Column D, we'll display these names in reverse order. So, the required formula in Cell D5 should be:
=INDEX(B5:B10, SEQUENCE(ROWS(B5:B10), , ROWS(B5:B10), -1))
Here, the SEQUENCE function reverses the row numbers of all names and the INDEX function later extracts the names in a reverse order based on the second argument (row_num) modified by the SEQUENCE function previously.
16. Preparing a List of Alphabets with SEQUENCE and CHAR Functions
In the last example, we'll see how the SEQUENCE function can be used to create a list of alphabets in an array. In the following picture, two separate rows have been used to display the array containing all alphabets.
The required formula in Cell B4 is:
In this formula, the CHAR function returns the characters specified by the Unicode. As there are 26 alphabets in English, we have used 13 columns here. We can also define the column number as 2 and the formula will return all the alphabets in 13 rows and 2 columns.
💡 Things to Keep in Mind
🔺 SEQUENCE function returns an array by spilling the values in multiple rows and columns. So, if any of the return values in the array cannot find an empty cell to represent itself then the function will return an #SPILL error.
🔺 SEQUENCE function is currently available in Microsoft Office 365 only.
🔺 The default value for all optional arguments of the SEQUENCE function is 1.
Concluding Words
I hope all of the methods mentioned above to use the SEQUENCE function will now provoke you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
Further Readings
- How to Use ROUND Function in Excel (With 9 Examples)
- Use RANDBETWEEN Function in Excel (4 Examples)
- How to Use SUM Function in Excel (With 6 Easy Examples)
- Exponential Notation E in Excel & How to Turn Off Auto Scientific Notation!
- How to Use INT Function in Excel (With 8 Examples)
Source: https://www.exceldemy.com/excel-sequence-function/
0 Response to "What Function is Used to Define and Continue Sequence of 2 6 12 20 30"
إرسال تعليق