Welcome to Excel magic trick number 1,406.
Hey, if you want to download this Excel workbook
file, so you can follow along, click on the link
below the video.
We got to talk about calculating a moving
average with averageifs and end of month function.
Now here's our data set.
It starts in January 2016.
Control, down arrow, and we have transactional records
all the way to December 2018.
Control, Home.
And over here, we want a list of each month and then
a 12 month moving average.
Now all that means is that if I have the end of the month
and I'm going to type 12/31/2016,
a moving average with this end of the month
would look at all the dates in December
all the way back to January 1st.
From this data set, that means we need the sales from January
1st all the way to December 31st,
and then we calculate the average.
But as I copy this down, it needs
to successively move, only looking at the last 12 months.
Now I want to copy this end of the month date down.
So watch this.
This is a great trick.
Right there in the corner, that little green box
is the fill handle, that's the selection cursor,
that big fat white cursor.
And when I hover over it, the crosshair or angry rabbit,
I can simply click and drag down.
And there, that little smart tag,
if you hover over it and click on the arrow, look at that.
For dates, there's a bunch of options including
the one we want, Fill Months.
Now someone actually asked this question
in the comments a couple days ago,
what's the keyboard once you copy this down
to open up the smart tag?
I never use it because it's a kind of long one.
Shift, Alt, F10 is the keyboard to open it up.
And then you can arrow down.
So I'm going to arrow down to months and then Enter.
So there we have our end of the months, including,
look at that, February 28.
Now in our formula here, we're going to use averageifs,
and we're going to have two conditions.
Remember we need all the sales that
are less than or equal to December 31, 2016,
and greater than exactly one year back, which is 1/1/2016.
Now I'm actually going to show you
the end of the months off to the side,
and then we'll copy it and use it inside of our averageifs.
All right, ready?
Equals end of the month.
Why would we use the end of the month function
to get the 1st of January?
Well, let's see how to do this.
I'm going to say the start date, December 31, 2016, comma.
And months, if I give it a positive number,
it'll give me the end of the month in the future.
Zero would give me the same exact date,
because zero means end of the month for the current month.
And minus goes backwards.
And that's what we want, minus 12.
Now if I close parentheses and Control, Enter,
that's the serial number.
Control one to open up Format Cells, Date.
That button is highlighted, so I can simply hit Enter.
Well, wait a second.
That's December 31, 2015.
F2 to put it in edit mode.
We have to add one more day to get to January 1st.
Control, Enter.
Now I'm going to F2 and copy this.
Control C. That is our lower limit.
Now let's come over here.
Equals averageifs.
This function was new in Excel 2007.
If I hit Tab, there is our screen tip.
Now average range, that's where we put the numbers that we
want to average.
Now this is an Excel table.
I've already converted it to an Excel table with Control T,
and then I named it.
And it's very convenient.
Because if we click at the top, there's
the column header or field name.
You see that downward pointy black arrow.
When you click, it automatically highlights the whole column.
And it uses proper table formula nomenclature, table name
and then the column name or field name in square brackets,
comma, criteria range.
Let me click on the top with my black downward pointing arrow,
boom.
There is order date, table name, field name in square brackets,
comma.
Now the first criteria is going to be greater than
or equal to the lower limit.
So in double quotes, greater than symbol, first character,
equal sign, second character, and then close double quotes.
Any time you use comparative operators,
we need them in double quotes and count if, some ifs,
and averages.
And then we're going to join it to our lower limits,
so I use ampersand, Shift seven, Control V.
Now just like in our last video, we're
not going to have to put parentheses around that plus
to get it to calculate correctly,
because the order of precedence for Excel to calculate formulas
always calculates math operators before join operators
and comparative operators for that matter.
So there is are lower limit.
Click, comma, criteria range, I'm simply
going to use my black arrow.
Boom.
That is so awesome and convenient.
Comma, and now we go in double quotes less than
or equal to in double quotes join it to the upper limit.
Close parentheses.
And there we go.
That formula right there will calculate
a moving average all the way down,
always picking out the sales numbers that
are 12 months back.
Control, Enter.
Double click and send it down.
I'm going to the last cell, F2, and look at that.
All of the cell references are pointing to the right place.
Now we have one last thing we'd like to do here
since this is a report.
I'm going to highlight all the end of the months.
Control one to open up Format Cells.
And let's do a little custom number formatting.
Now custom number formatting for dates is easy.
M is month.
D is day.
And Y is year.
So I'm going to highlight the Type box.
Notice the sample right there.
When I type a M, that gives me the actual number.
Two Ms would give me a lead zero for like month one.
And look at this, three Ms gives me three letter abbreviation,
and four gives me the full December.
I'm going to backspace and only use three characters.
Space, dash, space.
And then watch the year.
YY, that gives me two.
And three always gives us the full year.
And that's our custom number formatting.
Click OK.
And there is our report, custom number formatting
showing month and year, and there is our averageifs and end
of month function.
All right, that was a little fun with moving average.
We'll see you next video.