Rod sends in today's question. How can his pivot table indicate if ALL of the records for a certain level are marked as complete? Episode 989 will show you a use for the PRODUCT calculation in a pivot table. Also - the arcane custom number format to display positive, negative, zero values in different manners.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See how we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Oh! Hang on, before we get to today's problem.
I have a quiz, it's a pop quiz that's right.
Hey! If anyone out there is teaching Excel class, you should give your students at this pop quiz today.
Here we go. Question A: Fill in the answers.
Answer 9 has been given for you, and answer 9 is SUM.
Go ahead click pause on the iPod, grab a piece of paper write this out.
Alright, well what the heck could the question B?
SUM number nine SUM.
Well if you have ever done a subtotal you recognize that using the Subtotal function nine is the argument to give us a sum and what are these other arguments?
I know after SUM is variance and variance P and before its standard deviation P and before that just regular standard deviation.
AVERAGE is the first one then either COUNT or COUNTA, I'm gonna go with COUNT in and COUNTA.
Alright! Now, few others left here.
I know MIN and MAX are in there.
What's the other one?
I'm to go looking product.
Why don't waste forget product? Okay! So, Max, min, Product.
That's the way that I would answer this quiz.
I think that's the right answer now.
You can go check the subtotal function.
The point here is that when we use the subtotal function.
There are 11 different calculations that we can do.
Sure we use SUM all the time COUNT or COUNTA once in a while.
But frankly, I've never used any of the other Eight of them and these same 11 calculations are in a pivot table.
Now, when I got this next question from Rod, last night.
I have to tell you I did not think of those 11 calculations.
Rod says, "Hey! I have data. It's an outline form." There's level 1 and level 2.
I have some amount and then I have a field as a binary field.
As either says is this step complete or not complete and I need to see for level one is every single item in level one complete and...
So, yesterday, I wrote a sum product formula.
Basically, they said hey go see if all of the level ones that match this row have the complete field equal to a '1' or not.
Input you know one or a zero there, but I woke up this morning I said wait a second.
There might be a way to do this just naturally using the pivot table.
So, insert pivot table, I'm going to put it right here next to our data that way we can see the data as well.
Click [ ok ] and I'll put level 1 and level 2 along the side of course amount is easy going to sum that up.
Naturally, I'm going to take the complete field and put the complete field there And you see that we get to see how many are complete, but that's not what I'm interested in.
I want to change that calculation.
So, I'm going to choose SUM of complete and go into field settings.
Where you see that we have the same 11 calculations here, including product.
Well, what is product?
Product takes all of those numbers and multiplies them together.
So, think about it if all of the completes are one then this is going to show up as a one if any single step is not complete.
Then that's 0. 1 times 1 times 1 times 0 is going to cause this result to be 0.
Click [ ok ] and we get to very quickly see which items are complete.
So, here item A step 2.
Everything is complete, which means that because all of step 1, 2 and 3 are complete, then A is complete as well.
Now, down an item B, step one is complete but step two and three are not complete.
So, the product of one times 0 times 0 is 0 and we get incomplete.
Alright! So this is actually the answer we want.
The problem is going to be for Rod, to convince the people who read this to understand that one means complete and zero means incomplete.
We're going to go very very arcane here to solve this problem I'm going to go into field settings and choose number format and well, you've probably been in the custom number formats before.
There is a very unusual type of number format and it has the Zones.
We're putting in three zones.
What to do if it's positive?
What to do if it's negative?
What to do with it zero?
Each zone, separated by a semicolon (;).
So, in quotes complete that's what I want to see if the answer is positive, and then a semicolon if it's negative which it will never be incomplete and then another semicolon and again if it's zero, incomplete.
The last one would be text, that's not an issue here.
So, I'm not going to put the fourth zone in, click [ ok ], click [ ok ] and there you see that we change those zeros and ones to complete and Incomplete.
Very, very cool using the product function.
I have to say in 20 years of using Excel.
This is the first time, I've ever actually had a use for the product function within a pivot table.
Thanks to Rod for sending that question in.
Thanks to you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See how we can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Oh! Hang on, before we get to today's problem.
I have a quiz, it's a pop quiz that's right.
Hey! If anyone out there is teaching Excel class, you should give your students at this pop quiz today.
Here we go. Question A: Fill in the answers.
Answer 9 has been given for you, and answer 9 is SUM.
Go ahead click pause on the iPod, grab a piece of paper write this out.
Alright, well what the heck could the question B?
SUM number nine SUM.
Well if you have ever done a subtotal you recognize that using the Subtotal function nine is the argument to give us a sum and what are these other arguments?
I know after SUM is variance and variance P and before its standard deviation P and before that just regular standard deviation.
AVERAGE is the first one then either COUNT or COUNTA, I'm gonna go with COUNT in and COUNTA.
Alright! Now, few others left here.
I know MIN and MAX are in there.
What's the other one?
I'm to go looking product.
Why don't waste forget product? Okay! So, Max, min, Product.
That's the way that I would answer this quiz.
I think that's the right answer now.
You can go check the subtotal function.
The point here is that when we use the subtotal function.
There are 11 different calculations that we can do.
Sure we use SUM all the time COUNT or COUNTA once in a while.
But frankly, I've never used any of the other Eight of them and these same 11 calculations are in a pivot table.
Now, when I got this next question from Rod, last night.
I have to tell you I did not think of those 11 calculations.
Rod says, "Hey! I have data. It's an outline form." There's level 1 and level 2.
I have some amount and then I have a field as a binary field.
As either says is this step complete or not complete and I need to see for level one is every single item in level one complete and...
So, yesterday, I wrote a sum product formula.
Basically, they said hey go see if all of the level ones that match this row have the complete field equal to a '1' or not.
Input you know one or a zero there, but I woke up this morning I said wait a second.
There might be a way to do this just naturally using the pivot table.
So, insert pivot table, I'm going to put it right here next to our data that way we can see the data as well.
Click [ ok ] and I'll put level 1 and level 2 along the side of course amount is easy going to sum that up.
Naturally, I'm going to take the complete field and put the complete field there And you see that we get to see how many are complete, but that's not what I'm interested in.
I want to change that calculation.
So, I'm going to choose SUM of complete and go into field settings.
Where you see that we have the same 11 calculations here, including product.
Well, what is product?
Product takes all of those numbers and multiplies them together.
So, think about it if all of the completes are one then this is going to show up as a one if any single step is not complete.
Then that's 0. 1 times 1 times 1 times 0 is going to cause this result to be 0.
Click [ ok ] and we get to very quickly see which items are complete.
So, here item A step 2.
Everything is complete, which means that because all of step 1, 2 and 3 are complete, then A is complete as well.
Now, down an item B, step one is complete but step two and three are not complete.
So, the product of one times 0 times 0 is 0 and we get incomplete.
Alright! So this is actually the answer we want.
The problem is going to be for Rod, to convince the people who read this to understand that one means complete and zero means incomplete.
We're going to go very very arcane here to solve this problem I'm going to go into field settings and choose number format and well, you've probably been in the custom number formats before.
There is a very unusual type of number format and it has the Zones.
We're putting in three zones.
What to do if it's positive?
What to do if it's negative?
What to do with it zero?
Each zone, separated by a semicolon (;).
So, in quotes complete that's what I want to see if the answer is positive, and then a semicolon if it's negative which it will never be incomplete and then another semicolon and again if it's zero, incomplete.
The last one would be text, that's not an issue here.
So, I'm not going to put the fourth zone in, click [ ok ], click [ ok ] and there you see that we change those zeros and ones to complete and Incomplete.
Very, very cool using the product function.
I have to say in 20 years of using Excel.
This is the first time, I've ever actually had a use for the product function within a pivot table.
Thanks to Rod for sending that question in.
Thanks to you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
