- Excel Version
- 365
As of November 26, 2019, the Excel team has announced that Dynamic Arrays have rolled out to 20% of Office 365 Subscribers on the Monthly channel. This means tens of millions of people are getting their first look at SORT, SORTBY, FILTER, UNIQUE, RANDARRAY, and SEQUENCE.
In my seminars, I always talk about SORT, FILTER and UNIQUE as being the rockstars of these new functions, painting SEQUENCE as being a lowly Clark Kent to the superhero functions. But, as I reveal later in the session, SEQUENCE is the lowly function that turns almost every function into an array function.
During one of my last sessions, held in Springfield Missouri, Lisa, a data analyst sitting in Row 3 of the seminar suggested a great use for SEQUENCE. She frequently has to build a report that shows the last 52 weeks, in descending sequence. Every row represents a Monday starting from this previous Monday and going backwards.
Since I am writing this on Friday, November 29, 2019, I am going to start by entering the most recent Monday in cell D1. With my US date settings, that means 11/25/2019 is in D1. Add a heading in A3.
The SEQUENCE function has one required argument and three optional arguments:
Rows
[Columns]
[Start]
[Step]
For this example Rows will be 52. Columns could be omitted or you could use 1. The Start value will be the date in D1. The Step value will be negative 7, so each date goes backwards one week.
Type =SEQUENCE(52,1,D1,-7) and press Enter.
This gets you the correct answer, but the wrong format:
Select A4:A55 (From A4, you can press Ctrl+Shift+DownArrow).
The first thing you might notice is that the Name Box is using the Array Formula Nomenclature of A4# where the Hashtag means the entire dynamic array currently generated by the formula in A4.
Apply a Long Date format and you have successfully generated 52 weeks in descending sequence:
While this solves Lisa's problem, do we really need the date to be entered in cell D4?
What is the real reason I entered the date in cell D4? It is because I, Bill Jelen, author of 60 books about Excel, don't know how to enter a date as an argument in another function. I know that I could use =SEQUENCE(52,1,DATE(2019,11,25),-7) but that seems like cheating.
I tried entering =SEQUENCE(52,1,11/25/2019,-7) but this failed because Excel tried to do the math of 11 divided by 25 and that quotient further divided by 2019. This is clearly not what I want.
I actually Googled for "how to use a date as an argument to another function" but none of the results were what I needed.
After a little experimenting in the Function Arguments dialog box, the solution that did work was to wrap the date in quotes. The Function Arguments showed that Excel converts "11/25/2019" to a serial number of 43,794. Normally, as Excellers, we hate numbers stored as text, but apparently this is one way to solve the problem.
Do you have a better solution to the problem of using dates as arguments in Excel functions? If you do, leave a comment below.
In my seminars, I always talk about SORT, FILTER and UNIQUE as being the rockstars of these new functions, painting SEQUENCE as being a lowly Clark Kent to the superhero functions. But, as I reveal later in the session, SEQUENCE is the lowly function that turns almost every function into an array function.
During one of my last sessions, held in Springfield Missouri, Lisa, a data analyst sitting in Row 3 of the seminar suggested a great use for SEQUENCE. She frequently has to build a report that shows the last 52 weeks, in descending sequence. Every row represents a Monday starting from this previous Monday and going backwards.
Since I am writing this on Friday, November 29, 2019, I am going to start by entering the most recent Monday in cell D1. With my US date settings, that means 11/25/2019 is in D1. Add a heading in A3.
The SEQUENCE function has one required argument and three optional arguments:
Rows
[Columns]
[Start]
[Step]
For this example Rows will be 52. Columns could be omitted or you could use 1. The Start value will be the date in D1. The Step value will be negative 7, so each date goes backwards one week.
Type =SEQUENCE(52,1,D1,-7) and press Enter.
This gets you the correct answer, but the wrong format:
Select A4:A55 (From A4, you can press Ctrl+Shift+DownArrow).
The first thing you might notice is that the Name Box is using the Array Formula Nomenclature of A4# where the Hashtag means the entire dynamic array currently generated by the formula in A4.
Apply a Long Date format and you have successfully generated 52 weeks in descending sequence:
While this solves Lisa's problem, do we really need the date to be entered in cell D4?
What is the real reason I entered the date in cell D4? It is because I, Bill Jelen, author of 60 books about Excel, don't know how to enter a date as an argument in another function. I know that I could use =SEQUENCE(52,1,DATE(2019,11,25),-7) but that seems like cheating.
I tried entering =SEQUENCE(52,1,11/25/2019,-7) but this failed because Excel tried to do the math of 11 divided by 25 and that quotient further divided by 2019. This is clearly not what I want.
I actually Googled for "how to use a date as an argument to another function" but none of the results were what I needed.
After a little experimenting in the Function Arguments dialog box, the solution that did work was to wrap the date in quotes. The Function Arguments showed that Excel converts "11/25/2019" to a serial number of 43,794. Normally, as Excellers, we hate numbers stored as text, but apparently this is one way to solve the problem.
Do you have a better solution to the problem of using dates as arguments in Excel functions? If you do, leave a comment below.
