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.