Welcome to Excel Magic Trick number 1,397, part 2.
Hey, if you want to download this Excel workbook file
so you can follow along, click on the link below the video.
Hey, last couple of videos we've been
talking about running total.
And the last video, 1397, we actually
talked about Excel formulas for running
total percent of running total.
But in that video, are running total was for a count
so in this video we want to see how
to do monthly revenue running total,
and then a percent of total revenue earned by month chart.
Here's our data set.
We're going to figure out from the min and max date
how to calculate a column of lower and upper dates,
then calculate the actual revenue for each month running
total percent running total, and then create this chart.
Now let's go over to the sheet 1397 part 2.
Now here's our data set.
And I've already converted this to an Excel table
by going up to Insert and clicking on that button
right there, or using the keyboard Control-T.
And I very importantly, went up to Table Tools Design
Properties and named it.
So the name of this table is F amount.
Now the next thing I did was I went over
and calculated the min and max from this column
so I knew what the lower and upper limit for each month
would be, and the total span of months.
Now let's look at the formula here, F2.
Course we use the min function.
But check that out.
That's called Table Formula Nomenclature
or Structured References.
F amount, you always have the table name, then
in square brackets, the field name.
So that's the field name date.
So I calculated the min and max.
From those, we can create our upper and lower limits
for then using sum ifs to calculate the monthly revenue.
Now we're going to do this with formula.
So the first formula will be a single formula
that looks at the min, tab.
Now we need a formula that will calculate the end of the month,
so of course, we'll use the end of the month function.
I'm going to use Left arrow.
That's the start date, comma.
And to get the end of the month for last month, I put minus 1.
To get the end of the month for the current month, January,
I put 0.
If I'm getting the end of the month for next month,
I'd put one or two months ahead too.
So for end of the month in January, we type a 0.
And the good news, of course, is end of the month,
we'll know leap year too.
So when I hit Enter, there's the end of the month.
Now I'm going to create the first day in February.
And I could use e-date and point to this.
But I'm simply going to say hey, look at the previous end
of the month and add one.
Either one of those formulas, this formulas or e-date
would work.
Control-Enter and then copy it down.
Whoa!
They're all-- well, now I have to double click and send
this one down.
And sure enough, now the whole two columns
of dates for lower and upper will work.
Now revenue, we need to sum from the revenue column
any time the data and this column
is greater than or equal to the lower date and less than
or equal to the upper date.
Any time we have adding with multiple conditions,
we use the sum ifs functions.
Now, in last video we did counting, and then running
totals and percent running total for counting.
So in that video, we use count its or the frequency function.
Here, we're not counting, we're adding for a particular period.
So the sum range, that's all the numbers
you potentially want to add.
Now, let's learn a great trick here.
If you hover your cursor right there,
that's the selection cursor.
But right near the top of the field
name, in an Excel table, that downward pointing black arrow,
when I click--
look at that.
That is an amazing feature in Excel tables.
And it's got the proper syntax--
table name and field name in square brackets.
Now that's all the numbers to add,
but we don't want all of them.
So we need to tell sum ifs the criteria,
but first, we have to give it the criteria range.
So watch this.
I'm going to do the same trick.
Above date, when I see that black downward pointing arrow,
I click.
There it is, table name, field name in square brackets, comma.
Now we have two conditions.
The first condition or criteria is
going to be greater than or equal to that.
Now count if sum ifs and similar functions
require that the comparative operator is in double quotes.
So I have to say greater than or equal to.
And those are two separate characters, and double quote.
Now I need to join it, so I use the join symbol ampersand,
ship 7, and I click on the lower date.
Now notice that entire construction right there
might be a little bit confusing, but here's how I remember.
Notice the big side is pointing away, as if
pointing towards the column.
So it will look through and find any number here
that is bigger than or equal to this lower.
So 112017 will be included, 1, 2, 1, 3 and so on.
Now that would get every single date in the table.
So that's not enough.
We have to type a comma and put the second criteria range.
Love this trick right here, downward pointing
black arrow click.
And there we go, comma.
Now we have to for criteria 2, in double quotes say also,
in an and logical test, the date as to simultaneously
also be less than or equal to, in double quotes,
joined to the upper date.
So anytime it gets true, true, it will use the date
and pick out the correct revenue number.
Now I close parentheses, Control-Enter, double click
and send it down.
I'm going to go to the last cell and hit F2.
And those cell references are looking good.
I should have done that over here too, F2 and right here,
F2.
Those are all looking good.
Now running total-- the last video
we use the sum function with an expandable range,
which is a great efficient form [INAUDIBLE] for small data
sets.
But let's see a different way of calculating running total.
Well, the first period or category
always is going to be equal to the first amount.
So I'm just going to have a single different formula
in this column.
Hit Enter.
Now all of the remaining formulas
will be whatever the current amount
is plus whatever the previous balance
from the previous period is.
Those are both relative cell references,
Control-Enter, double click, and send it down.
And so for any particular cell, the logic will be oh yeah,
that's the running total for the previous period
plus the current period.
That's going to work all the way down.
And that formula in general, is preferable on really large data
sets, because every formula is only running two cell
references, whereas an expandable range
like we saw last video, might have a huge range of values
to look over.
Escape-- now our percent running total--
I'm going to say always compare the current period.
And I use my Left arrow to get a relative cell
reference divided by--
and either click down here on the grand overall total,
or watch this.
I'm going to keyboard, Left arrow, Control-down arrow,
and then F4 to lock it.
That division, where we're comparing each relative cell
reference period running total to the grand overall total,
will work.
Control-Enter, double click and send it down.
And just like we saw last video, the meaning
of a percentage like this is that we earned about 25%
of our total revenue for the year by the end of August.
Look at that.
We weren't even above 50% when we got to the end of October.
66% of the revenues were earned by the end of November.
And of course, 100% by the time we get to December.
So it looks like we kept most of our revenues at Christmas.
Now we want to see how to create a chart.
And I'm going to use a line chart.
And the reason we can use a line chart
for either a running total or percent running total
is because the increments are the same for each category.
Actually, in our next video, I'll
show you running totals for when you have the increment
is the same, and when you don't have
an increment that is the same, meaning we have month here.
If we didn't have the same increment, like, different time
periods or for counting different increments
from the lower and upper, then you'd
have to use that xy scatter.
But our increments so the same.
So I'm going to highlight the upper column,
hold Control to highlight range is not next to each other,
non-contiguous ranges, and then highlight the percent running
total--
two ranges.
Now I can go up to Insert over to line.
And I'm going to use this one right here, line with markers.
Now we're going to fix this up just like we did last video.
I'm going to click on the chart title, and with a solid line,
I want to link it to this title.
So I type an equal sign that jumps me up to the Formula bar,
click on the cell J6.
I see my formula, Enter.
Come over to the green plus.
I want data labels.
I want to see those percentages.
Now we have chart junk.
We have percentages in two places.
So I click on the access delete.
Click on the chart background in Control-1
to open up the Task pane.
I want to fill it with a solid fill.
That's too dark.
Let's pick a lighter one, something like that color
there.
I'm thinking I want a different sort of label here.
And the lucky thing about data like this--
in fact, these percentages and these dates
are coming from our data set.
If I come over here and apply a custom number format
that shows me the month, name, and year, that
will be reflected in our chart.
So highlight the range Control-1 down to custom.
And for custom number formatting,
M means month, D means day, and Y means year.
Now you can play around with this,
teach yourself custom number formatting.
If you type an M, gives me a single digit, two M's, it
gives me a leading 0 if there's only a single digit.
Three M's, it's the three letter abbreviation,
and four is the full month name.
I'm going to leave it as three M's, space.
And then year is slightly different,
one, two, that gives me always the two digits with the lead
in zero, and three gives me the full four digits.
So that is the custom number format
and they use to show month and year.
Click OK.
And there we can see it in the cells and in our chart.
That is looking pretty good.
So for any particular place as of the end of July,
we earn 17.8% of total revenues for the year.
I can see I'm going to change the column width up here.
All right, that was a little fun with Excel tables.
We did some date calculation here, including end of month.
We saw to use the sum ifs for the actual monthly revenue.
We did a simple two-step formula for running total,
and then a percentage formula for percent running total,
and our percent of total revenue earned by month chart.
Now, next video, 1398, we'll see how
to do running total percent running total
with DAX formulas.
All right.
We'll see you next video.