27
Mar

Excel Trailing 52 Weeks With SEQUENCE array formula – 2308


Learn Excel from MrExcel
Podcast Episode 2308. 52 trailing weeks with SEQUENCE.
Hey, welcome back to MrExcel Netcast. I’m Bill Jelen today
we’re going to look at one of the new dynamic array functions
called SEQUENCE and when this one initially came out I said oh
this is this is really silly but SEQUENCE can do a whole bunch of
things for arguments or in the first one is required which is
the number of rows and optionally the number of
columns, the starting number and the step number. If you don’t
provide these three it is going to be 1 column wide starting at 1, stepping by 1. So just
very easily here SEQUENCE(, let’s say 1000) will generate
1000 numbers like that right? Super easy to do but you can
customize this a little bit if we want to go from 5 to 10,
jumping by one:=SEQUENCE And see I need 6 rows. Don’t
need it to be more than one column so I can skip the number
of columns. Start at 5. I don’t need to say it’s stepping by one,
just close and I get 5 to 10. Here. Let’s do odd and even
numbers.=SEQUENCE(. I’m going to do 10
rows two columns. And I get the odd and even
numbers from one to 20 broken out. But hey, this one – this was
cool I was doing a seminar – one of my last live seminars – in
Springfield, Missouri. Lisa was there and I was
going over SEQUENCE and Lisa shouted out from the back
and said, Hey, we have a report we have to create 52 trailing
weeks. So going from today backwards in the last 52 weeks
and SEQUENCE would be great if you were to have 52 rows and one column. And we do we start? TODAY(). Today is a great function that gives us
the current day and then the step is going to be negative 7. That way we’ll go from today’s date and then the
previous week. The previous week, the previous week, and so
on. Like that right now. One hassle with this is that
sequence is expecting number, so it’s not smart enough to format
things as dates. I’ll choose all that data, control shift down
arrow, and then choose short date or long date or whichever
you need. This reminds me of another one from Excel MVP
Roger Govier: Creating a forward-looking calendar so=SEQUENCE(. We we want 5
rows, 7 columns because there Seven days in a week starting
from today and then the step will just automatically be one
and again right numbers but wrong format. Format that as a short date. Actually, you know,
since it’s a calendar, we can do this control one and come in here.
Just something like that perfect and then to get the days
of the week will say equal text. This is pretty cool. I’m gonna
choose Seven numbers here. Text is expecting just one number
there. But now thanks to dynamic arrays, TEXT can accept all
Seven and the format will do DDD to spell out the
day abbreviation, something like that right. Great, great new
function called SEQUENCE. You know it’s not as sexy as
SORT and FILTER and UNIQUE, but there’s a lot of very cool
things that we can do with SEQUENCE. Now the Excel Dynamic
Arrays book. I know a lot of you have those first edition, I
gave the first edition for free. I’ve updated it to a
second edition. Just $3. Click the “i” in the top right hand corner. If
you like what you see here on the podcast please
SUBSCRIBE and ring that bell so you get notifications
when there is a new video. Feel free to post any
questions or comments down in the comment section
below. I want to thank you for stopping by. I’ll see you
next time for another netcast from MrExcel.

Tags: , , , , , , , , , , , , , , , , , , , , , , ,

There are no comments yet

Why not be the first

Leave a Reply

Your email address will not be published. Required fields are marked *