Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
A great question from Richard; great because it has several different ways to solve it, some of which are fairly geeky and fairly tough.
Richard has a Pivot Table, and basically, he has different reason codes down the side, and different branches going across the top.
And you'll see that here, in Q1, there were three different reasons.
And so, with the Grand Totals in Row 8.
As he switches to Q2 though, there were more reasons, so the Grand Total is in 10; and in Q3, there are different number reasons, so the Total's in 9.
He wanted to write a formula out here that's going to always show the percentage of the total.
Now, he only wanted it for the Grand Total, that's why he didn't build it as part of the Pivot Table.
And so, of course, the problem that you have is, if you use =G5/G9, well, that G9 is going to change every time.
And so, there's a really cool use of VLOOKUP; not the VLOOKUP that most of us use with the ,FALSE at the end, but the other version of VLOOKUP.
If we use =VLOOKUP and we look up some large number-- so I'm just going to search for 99999999 within the range Column G, first column-- and then put ,TRUE at the end instead of ,FALSE-- now you can actually leave comma throughout.
This is an amazing version of VLOOKUP that, basically, is going to give us the last numeric item in the table-- not the largest but the last.
Now, it happens, in this case, that the Grand Total is probably going to be the largest, so we could have used max; but I like this VLOOKUP because it's a very clever way to go, just looking for some number larger than any number you could possibly want, and it's going to return that last value.
And just by way of example, if I would put, for example, a 17 here, that formula over here is going to return a 17.
So, very clever little formula.
So, one solution for Richard is to build a form that says, "Hey, we're going to take =G5 divided by the VLOOKUP of some large number within this range-- large range of Column G, ,1 ,TRUE-- and that will show that that particular reason was 11%.
I want to copy this down through several cells, we will get the percentage of the total, and we will find, as you go through and choose a different quarter-- Q1-- it is still dividing by the right number instead of some other number.
So, very cool solution.
Now if you want to avoid this entirely, a different solution is to come here to the reason code; I'm going to double-click on Reason; and there's a setting here called "Show items with no data, and what this is going to do is make sure that all five reasons show up every single quarter-- so even if there was no data for one of those reasons, it will show up.
Now, we could always predictably know that the Grand Total is going to be in Row 10, and we could solve the problem in that way.
So, a couple of different solutions and, also, assuming there were no negatives, which, in this case, since its a count, there wouldn't have been.
We could have also used the max of Column G to return the largest value.
A couple of different solutions to Richard's problem of how to make sure that that percentage of total over here is always dividing by the correct number returned from the Pivot Table.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
A great question from Richard; great because it has several different ways to solve it, some of which are fairly geeky and fairly tough.
Richard has a Pivot Table, and basically, he has different reason codes down the side, and different branches going across the top.
And you'll see that here, in Q1, there were three different reasons.
And so, with the Grand Totals in Row 8.
As he switches to Q2 though, there were more reasons, so the Grand Total is in 10; and in Q3, there are different number reasons, so the Total's in 9.
He wanted to write a formula out here that's going to always show the percentage of the total.
Now, he only wanted it for the Grand Total, that's why he didn't build it as part of the Pivot Table.
And so, of course, the problem that you have is, if you use =G5/G9, well, that G9 is going to change every time.
And so, there's a really cool use of VLOOKUP; not the VLOOKUP that most of us use with the ,FALSE at the end, but the other version of VLOOKUP.
If we use =VLOOKUP and we look up some large number-- so I'm just going to search for 99999999 within the range Column G, first column-- and then put ,TRUE at the end instead of ,FALSE-- now you can actually leave comma throughout.
This is an amazing version of VLOOKUP that, basically, is going to give us the last numeric item in the table-- not the largest but the last.
Now, it happens, in this case, that the Grand Total is probably going to be the largest, so we could have used max; but I like this VLOOKUP because it's a very clever way to go, just looking for some number larger than any number you could possibly want, and it's going to return that last value.
And just by way of example, if I would put, for example, a 17 here, that formula over here is going to return a 17.
So, very clever little formula.
So, one solution for Richard is to build a form that says, "Hey, we're going to take =G5 divided by the VLOOKUP of some large number within this range-- large range of Column G, ,1 ,TRUE-- and that will show that that particular reason was 11%.
I want to copy this down through several cells, we will get the percentage of the total, and we will find, as you go through and choose a different quarter-- Q1-- it is still dividing by the right number instead of some other number.
So, very cool solution.
Now if you want to avoid this entirely, a different solution is to come here to the reason code; I'm going to double-click on Reason; and there's a setting here called "Show items with no data, and what this is going to do is make sure that all five reasons show up every single quarter-- so even if there was no data for one of those reasons, it will show up.
Now, we could always predictably know that the Grand Total is going to be in Row 10, and we could solve the problem in that way.
So, a couple of different solutions and, also, assuming there were no negatives, which, in this case, since its a count, there wouldn't have been.
We could have also used the max of Column G to return the largest value.
A couple of different solutions to Richard's problem of how to make sure that that percentage of total over here is always dividing by the correct number returned from the Pivot Table.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
