Welcome to Excel Magic Trick number 1394.
If you want to download this Excel workbook,
Excel Magic Trick 1393-94 so you could follow along, click
on the link below the video.
Wow, we have a crazy video here.
We've got to calculate formulas for traditional pivot table
calculations, such as percentage of column total,
percent of parent total, and percent of parent row total.
Now in this video, we're going to see formulas.
It is so much easier to do this with a pivot table.
And in our last video, we saw how
to do all these calculations with a pivot table.
Why in the world would you want to do it with formulas?
Well, maybe you just want the challenge
of calculating the formulas.
But the real reason is if you want your solution
to update instantly when any source data changes
without having to refresh, then you'd have to use formulas.
Now let's look at our pivot table.
This column right here is actually
going to be quite tricky, because these two totals are
calculating a total from the revenue column based on product
criteria, but when we get to here
we need to get a total in the same column,
but from the manufacturer column we
have to get each individual manufacturer.
So let's start with that.
Now I'm just going to build a SUMIFS
formula based on the product and that
will be our starting point.
So I'm going to use the SUMIFS.
The sum range, I need the entire revenue range.
So I click in the top cell, Control, Shift, Down arrow
to highlight all the way to the bottom.
F4 to lock it, so it's locked when we copy down.
And it also jumps the screen back.
Comma.
Now criteria range.
We need to look through the entire Product column.
So I click on the top.
Control, Shift, Down arrow, F4, comma.
And then the criteria is simply going
to be relative cell reference.
Now I can close parentheses, Control,
Enter, and copy it down.
Now one thing it did right there is it wrecked the formatting.
So I'm going to point to my Smart tag
and say Fill Without Formatting, and now it didn't wreck it.
Well, this formula works for the products
but obviously not for here.
So we're going to have to run an IF formula
and check to see whether the cell in the product column
is empty and then run a different formula.
Now I'm going to come up to the top, F2.
I'm just going to see if I can get the If part of this
to work, IF.
And the logical test.
Well, I need to check if relative cell reference, one
to my left, is empty.
And the function in Excel is ISBLANK--
even though it should be called ISEMPTY.
There it is, relative cell reference, close parentheses,
comma.
So back in logical test argument,
that delivers a true or false.
Well, when we copy down to Channel Craft
total row is blank, we'll be delivering a true.
So comma, the value of false will be for the products.
Now we just have to change that little bit right there,
Value If True.
Well, it's SUMIFS again.
It's going to be the same sum range, Control, Shift,
Down arrow, F4, comma, criteria range.
That's the manufacturer.
Control, Shift, Down arrow, F4, comma.
And then the criteria.
Well, we're going to try this.
Because the actual criteria is over here.
It's not going to work.
Close parentheses.
We're almost there.
But we have the concept down.
There's the SUMIFS for manufacturer.
There's the SUMIFS for the product.
Now I need to close parentheses on the end.
Control, Enter.
It's still not going to work but we will fix it.
Double click and send it down.
Look at that.
It wrecked it again.
I'm going to show you a better way.
The non-edited formulas through the rest of the column--
that is the edited format.
So watch this.
I'm going to highlight the entire range.
In the active cell at the top I'm going to hit F2.
If I use Control Enter to populate this entire range
with this formula, the formatting
does not get changed.
So Control Enter.
And you could actually prove that to yourself.
What's going on?
Well, right here, of course, it can't
match Channel Craft, space, total with any of the Channel
Crafts over here.
So I'm going to come up to the top cell.
And for criteria, I'm going to use the SUBSTITUTE function.
The SUBSTITUTE says, hey, where is the text?
Well, it's right there.
Comma.
What's the old text you want to get rid of,
or substitute for, double quotes, space, total,
and double quote, comma.
What's the new text you want to substitute
for that space, total?
Well we use double quote, double quote.
And the formula will know to put nothing there.
Now I close parentheses on.
SUBSTITUTE and you could actually click criteria.
And there is the whole substitute.
That will work.
Control, Enter.
Actually, I'm going to highlight all the way down, F2, Control,
Enter.
And there we go, a single formula to calculate total
for the products.
But when it gets to this row right here,
it's calculating the total for Channel Craft.
Now I'm going to come to the bottom,
Alt Equals, that's adding it up, and Enter.
Whoa, wait a second.
That's not going to work.
Well, luckily we'll use an old accounting trick.
F2, it's exactly double, because there's subtotals.
So I'm going to divide by 2 and Enter.
All right.
Now we can simulate percent of column total,
each individual item divided over total.
So equals relative cell reference divided by I16 F4.
Control, Enter.
Watch this.
I have that cell selected.
I'm going to hover, hold Shift, click, F2, Control, Enter
to populate that all the way down without interfering
with the formatting.
And there's our percent of column total.
That one's easy.
Now percent of parent total, this is going to be tricky.
Because for this block right here,
we're going to have relative cell reference divided
by that total.
But when we get down to this next block,
the numerator for each will be relative cell reference.
But in all four cells, it's going
to have to have that total.
Well, I'm going to use the same trigger, that empty cell.
And we're going to use an expandable range.
I'm going to build this formula from the inside out,
starting with ISBLANK.
Then we're going to highlight the entire product column.
Now as I copy ISBLANK in this range down,
I need this range to be shrinking.
So every row, it needs to be looking
at a smaller and smaller range.
So how we're going to do that is I'm
going to double click the end cell reference in the range
and hit the F4 key and lock that one, but not the H4.
That way, as I copy down it will shrink.
Now I'm going to close parentheses.
And what we're doing here is that value argument in ISBLANK
is expecting one single cell.
We just gave it a bunch of cells.
If I click at the end and hit the F9 key to evaluate this,
you could see it's given me a series of TRUEs and FALSEs.
Now I want you to notice something here.
The first TRUE is exactly one, two, three down.
Control Z. Now I'm going to enter this and copy it down.
I want to click F2 and then F9, and notice now the TRUE
is in the second position, because it's a contracting
or shrinking range.
When I come down here, F2, F9, now the TRUE
is in the first position.
Those were all for Channel Craft.
Now when I get here, F2, F9, notice now
the TRUE is back in the fourth position,
meaning that's going to be our trigger for picking out,
for one, two, three, four, if there's some way to always pick
out the first TRUE as our contracting
range gets copied down, that could be a trigger for us
to pull this for our denominator.
So I'm going to come back up to the top, F2.
Now if I need to look for where the first TRUE is and I really
need the position, I'm going to use the MATCH function.
And what am I looking up?
I want to look up TRUE, comma.
That's that expandable range.
And the way you look up the first,
when there are duplicates, is you use exact match.
So I'm going to put a 0 here.
Close parentheses.
Now this is an array formula right there.
We're doing a function argument array operation.
So I'm actually going to enter this as an array formula.
Control, Shift, and Enter.
Now once we enter with Control, Shift, Enter,
we have to look up to the formula bar
and verify that those curly brackets are entered in.
Those curly brackets are entered in automatically by Excel
and they tell us that Excel understood and calculated this
as an array formula.
That's got the wrong formatting, but that's OK.
I'm going to copy this down.
And I'm going to apply general number formatting home.
Number, General, or use the keyboard.
Control, Shift, grave accent, or tilde.
Now how is that going to help us?
Well, 3, 2, 1, if I use the index function with a shrinking
or contracting range here, as I go down,
it will know to get the third one.
And then here, it'll know, get the second.
And then here, it will know to get the first.
When it comes down to the next row,
it'll get the fourth, third, and so on.
F2.
I'm going to use the INDEX function and the array.
Those are the items we're trying to look up,
the same exact range, comma.
And that last cell reference, we're
going to double click to highlight it, F4.
That is a contracting or shrinking range that
will work perfectly with MATCH.
MATCH is delivering the relative position for that shrinking
or contracting range.
Close parentheses.
We have to enter this with Control, Shift, and Enter.
Now if I double click and send it down,
that is how we're going to get the denominator
for each one of our sections of percent
of parent total calculations.
Now I can come to the top, F2.
Since I have the denominator right,
I simply, right before, INDEX put the relative cell reference
total revenue and use division.
Control, Shift, Enter.
Double click and send it down.
And there's our pattern of our percentage for each parent
total.
Now we can highlight this, Control 1, Number tab,
Percentage, click OK or Enter.
And there we have percent of parent total,
just as we had in our pivot table.
So the tricky part, of course, was that denominator.
Now I've see I've totally messed up the formatting.
Watch this.
I'm just going to highlight this column right here.
Right click.
And there over on the mini toolbar, I have Format Painter.
Then I'm going to click at the top,
and there that border is back.
Now we could take this one step further.
Right here, that is the array that's
causing us to have to use Control, Shift, Enter.
And I'm going to wrap that inside a function that
knows how to handle array calculations without Control,
Shift, Enter.
So I'm going to use the INDEX function.
Now INDEX has-- most of the time we're
using the first one, array, row number, and column number.
It is the array argument in INDEX
that can handle array operations without Control, Shift, Enter.
Now the problem is, we need all of the values that
is blank or spitting out.
Normally for INDEX, we'd give it a row number or row and column
number.
But watch this.
Because I need all the rows, I'm going
to type a comma to get to row number.
And you either put a 0 and then close off.
INDEX because a 0 tells INDEX to please give me all the rows,
or leave it omitted.
by putting a 0 or leaving row number omitted,
it says, hey, INDEX, get all of the rows in that range.
And now we don't have to use Control, Shift, Enter.
I'm just going to use Enter.
And then watch this.
I'm going to use that trick earlier, highlight
the whole range.
The top cell has our edited formula.
So I hit F2 and Control, Enter to populate it
all the way down.
Notice there's no curly brackets up there,
even though it's an array formula.
It's just able to calculate without using
that special keystroke.
All right, now we have one last formula,
percent of parent row total.
Well, as we talked about in the last video,
percent of parent row total is a combination
of percent of column total and percent of parent total.
So when we're doing percent of parent row total,
the actual individual amounts are
being compared to its parent.
But when we get down to the total for the manufacturer,
it doesn't have a parent, so we compare it to the column total.
So notice that percentage is really percent of column.
This one is really percent of parent total.
We already have our two calculations here.
So I'm going to use the same trigger, when
that cell is empty equals IF ISBLANK, right there.
That many-- relative cell reference.
Close parentheses.
Well, if that comes out true, that means we're down here.
And we need percent of column total.
So comma.
The value of, TRUE percent of column total.
Comma.
Value if false, percent of parent total.
Now notice, this formula is dependent on these two
other columns.
If we didn't have these two other columns,
we'd have to scoop that whole formula up here and put it
where the orange cell is, scoop that whole formula
and put it where K4 is.
Now close parentheses, Control, Enter.
Hover over the last cell.
Hold, Shift, click F2, Control, Enter
to populate that all the way down.
Whoops.
That's not going to work.
We actually have to amend this.
Because that is not empty.
So we're going to come up to the top, F2.
And instead of just ISBLANK, I need to test two things.
If it's blank, of course, I need this.
That would be the trigger.
But when I get down here, total.
So I need to say if it's blank or if it's equal to totals,
then please give me that percent of column total.
So up in logical test, I'm going to use an OR function.
The OR will run an OR logical test.
That's the first logical test.
I click after ISBLANK, comma.
Then I say relative cell reference all the way
over there, if you are equal to, in double quotes, totals,
in double quotes.
And then close parentheses on the OR.
Or, if it gets either one of these truths,
it'll take the orange relative cell reference.
Control, Enter, Shift, click F2, Control, Enter.
And there we go.
Wow, that was a wild, crazy formula to simulate
what we normally do in a pivot table.
We saw a Total Revenue, ISBLANK, and two SUMIFs,
and even a substitute.
Percent of column total was easy.
Percent of parent total, it was the denominator part
we had to get tricky with.
And then of course, this last formula, percent of parent row
total.
All right.
We'll see you next video.