Welcome to Excel Magic Trick and number 1,404.
Hey, if you want to download the six hour
workbook so you can follow along, click on the link
below the video.
Hey, we got to see how to calculate sales
per working day, and where to use formulas for this three
year report.
Now here's the final version.
We're going to have a lower and upper date.
We'll have calculate total sales.
And the tricky part will be number of working days.
Now we have our Sales table here.
There's the order date and sales.
And over here we have dates, and then
a second column with workday.
Now one of the problems here is this data
set, if I Control-Down arrow, it will continually
get new records.
So we're only up to 11/15/2009.
And we want our formulas to work and automatically calculate
correctly as we add new records.
If we go up to the Date table, the Date table
goes all the way down to 2011.
So this is our key to tell us which possible days are workday
and which ones are not a workday.
Control-Home.
The other problem too for us is when we copy the single formula
down, we want to make sure and get the proper count in August
here, because we're only up through in our Sales table
up through 11/15.
And then of course for December, we should get a count of zero.
Now let's go over to the sheet 1,404.
First, we want a single start date, and then all of our dates
to appear.
We're going to have a single cell that simply
points to the start date, Tab.
And then from that start date, we
can use the End of Month function.
So I'm going to take that relative cell reference, comma.
Months, if I give it one, it will give me
the end of next month.
Minus one gives me the end of last month.
And zero is what we want for the current month, Enter.
Now I can calculate a formula a couple different ways.
I'm going to say, hey, please get
the relative cell reference, the end of last month, plus one.
Now I can copy this one down.
And because I want subtotals in the middle,
I'm very carefully going to copy this down
just so that I see the first 12 months for lower and upper.
And then this row is going to be a different set of dates.
The formulas over here will work the same.
But I'm simply going to put the beginning of the year, Tab--
and then notice that one copied down.
That's not what I want.
I want to say equals.
And please give me the end of the year.
So once we have our patterns of dates,
I can copy these formulas, Control-C.
That one's not going to work, but watch this.
I'm going Control-V. And since that is improperly looking up
there, this one is looking to the end of the previous month.
So I'm going to copy that formula up.
Now I'm going to Copy, Control-C, Control-V,
and then these will be the full three year period.
So I'm going to say equals the very first date up
at the top, Tab, and then equals once cell above.
Now I did it this way.
We could have done this a couple of different ways.
But I wanted our formulas to always
have the right lower and upper date,
no matter what the granularity was,
whether it was month for the year,
or for the grand overall total for the year.
Oh, man.
Look at these little triangles here.
I'm going to highlight all of these,
and then point to the little smart tag and say Ignore Error.
All right, total sales is pretty straightforward.
We need to find from this column, only the sales that
are greater than or equal to the lower and less than
or equal to the upper.
The perfect function for that is the sum IFS function.
The sum range?
Now these are Excel tables.
I've already converted them to tables using Control-T
and then named them.
Actually, I'm going to hit Escape.
I already did Insert Table right there, or Control-T,
and I made sure and went and named these, equals SUMIFS.
And then we need our sum range.
And the cool thing about tables is
if we hover a cursor right at the top
and we see that black downward pointing arrow, I can click
and it puts the proper table name and field name or column
name in square brackets.
That's the convention when we use Excel tables.
Comma, criteria range.
And then I do the same thing.
When I see my black downward pointing arrow, boom.
There it is, F Sales, square brackets, Order Date, comma.
That's the first criteria range.
But we need criteria which is greater
than or equal to the lower.
In double quotes, we have to put our comparative operator.
So greater than or equal to and end double quotes.
That's the way SUMIFS works when we have comparative operators.
Now we need to join that to our cell reference.
So I use Shift-7, the ampersand.
And I'm going to click on the lower.
That construction there is very convenient,
because the same comparative operator will be used,
but that relative cell reference will always see the lower.
Now I type a comma, and I'll get the order date.
Again, black downward pointing arrow, click, comma.
Now we double quotes less than or equal to
for the upper, ampersand and then the upper date.
And that formula will give us our total sales.
Close parentheses Control-Enter.
Now I'm going to double click and send this
down, and very carefully go down to the bottom.
And when I see my smart tag, I'm going
to point to please fill without formatting, because I already
added bold.
Now notice because, we set up the upper and lower limit
for the granularity we wanted, our formula works.
Number of days is going to be the tricky part because, we
need to, in our counting formula,
look at the lower and upper here for the month.
But then we also need to look over here and find out
which days are work days and which ones are not.
So we're actually going to use COUNTIFS.
That will be the lower and upper criteria,
similar to the criteria we used for our sales formula.
But we'll always look over here at this date column
in our Date table.
So are you're ready?
Equals COUNTIFS in our criteria range.
I use my black downward pointing arrow.
Now these dates of course, that's
a unique list of all the dates from the first of the year
2005 all the way to the end of the year 2011.
Criteria range, comma.
Now we go hey, are any of you in double quotes
greater than or equal to, and join to the lower limit, comma.
We'll go over and get our dates, comma.
In double quotes, less than or equal to, for the upper limit,
ampersand.
And there is the upper.
Comma, and then our criteria range three.
That's going to be the work day.
So I see the work day, comma.
And then our criteria will be work day over here.
If this is something that's never going to change,
you could hard code it in, but I'm
going to leave it there like that.
F4 to lock it.
And that will almost work.
Close parentheses, Control-Enter.
And now I'm going to double click and send it down,
quickly go to the bottom, and say fill without formatting.
Now the problem is here, because it's
looking at the entire date table over here which
has all of the dates.
Remember, we need our formula to somehow recognize
that our Sales table only has dates up through 11/15,
I think it is.
So how could we do that?
Well, if we notice our formula is looking
at that as the upper limit, well,
what if instead of looking there,
we actually look over into our data
set, and say please find the max date that is less than
or equal to this upper limit.
Now this means we're going to be using the MAX function
with a single condition.
Now there's a few ways we can do this.
I'm going to use the brand new MAXIFS functions.
Now you have to have Excel 2016 Office 365.
And you have to be connected to the insider program.
If you don't have this function, I
will show you in just a moment how to do it
in all of the earlier versions.
But MAXIFS is exactly what we want here, the Max range.
Remember now we actually have to look through the order date.
So I'm going to click on that top.
When I see my black downward pointing arrow,
that's the Max range, comma.
Now the criteria range is going to be the same.
And watch this.
I'm going to click on Max range Control-C,
then click on criteria range Control-V. Now
comma, criteria, end double quotes, less than
or equal to, ampersand the upper limit.
So it will always be looking over here.
And for this month, it's looking for everything less than
or equal to 11/30.
Well, the biggest date it will find is 11/15.
If we were in this category right here, of course,
then it would find everything less than 10/31/2009,
and there are some transactions for that particular date.
So that will work.
Criteria 1, close parentheses.
So what we did there in COUNTIFS,
criteria too is we use MAXIFS to get in alternating or changing
upper limit for each one of the categories.
And notice when it gets down to this one,
it will try and find something greater than
and equal to 12/1 and in an and logical test,
something less than or equal to 12/31, and it won't find any.
So the count will be zero.
Control-Enter, now I'm going to copy it down, and then
point to that smart tag, fill without formatting.
And then copy it up.
And here's the smart tag, fill without formatting.
That was the hard part.
Now we can do our sales per working day.
But let's first look at how to do this in earlier versions.
Now if you have 2007 or earlier, well, we don't have an axis,
but we can simply put the if function right inside the Max,
saying if that range is directly less than
or equal to that upper limit, then put those values in.
So we simply put the IF.
Now this is an array formula, so you
have to enter it with Control, Shift, and Enter.
But I'm going to hit Escape.
In 2010 or later, we would use aggregate function.
The aggregate function has a number of different functions.
14 is large.
The 6 tells the function to ignore errors.
Then we take the actual order dates and divided
by the logical test.
Any time there's a false, it'll get an error.
So in essence, the errors are filtering out the values.
And then because we have 1 and k,
aggregate will always get the Max.
All right.
So three different ways to do our number of working days.
Now sales per working day is straight forward equals, hey,
I'm going to take total sales divided
by number of working days, Control-Enter, double click
and send it down.
And point to the fill handle fill without formatting.
Now how do we get rid of that divide by zero?
Well, we can use the IF function.
Now notice, divide by zero means we have a zero here.
And all the other circumstances will have
numbers or positive numbers.
F2, well, we could definitely use the IF function,
but guess what?
Comma, the value is true would be that formula.
But let's go back to the logical test.
Logical test means we need a true or false here.
And luckily, Excel interprets zero as false,
and any non-zero number as true.
So I can simply use the actual number, relative cell reference
as my logical test.
When it sees a number there meaning true,
it'll run the formula value of true,
otherwise I'm going to put a zero for value false.
Close parentheses, Control-Enter, double click
and send it down.
Come to the bottom.
Where is that smart tag?
Fill without formatting.
And there we go.
Now, as we fill out our records in our Sales table,
these formulas will automatically update.
And I only did this to 2009.
And we certainly could have built this all the way down
to the 2011 total.
All right.
That was a little fun with calculating
sales per working day.
We saw how to use end of month in a regular straight formula,
copy that down, how to use SUMIFS
for total sales, COUNTIFS and some sort of MAXIFS
for a number of working days, and then
the IF function for sales per working day.
All right.
We'll see you next video.