Create an average by each category that will work before AVERAGEIF came along in Excel 2007. Bill and Mike offer a pivot table, and two different formulas.
Transcript of the video:
Hey, welcome back, it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin ExcelIsFun.
This is our episode 142: Average of Column B for each Unique Value in Column A.
Bill Jellen: All right Mike we have a great question 7 by Sean at YouTube.
He has a very large table, much larger than this table, there's codes in column A and then percentages in column B and for each unique code in column A he needs to get the average of the percentages over in B. He tried using an index and match, but it returns only the top value.
Also tried nesting an if statement but ran into a problem with 7F statements that tells me that he is in Excel 2003 or earlier otherwise we could go to 32 if statements.
But, if statements aren't the way to solve this I have lots of different ideas in mind, but I'm going to go to the old tried-and-true pivot table.
First, so we have one seldom data selected insert pivot table, existing worksheet, click next to our data click OK.
Checkmark code which will send it to the wrong spot I'm going to undo that instead of a check mark I’m going to drag code down to row labels and then I want to check mark percentages which is giving me a sum of percentage by default but we can change that click right there on the first time of percentage field settings change to average click OK.
We're really done at this point.
I'm going to go back to design change the report layout in tabular form to get real headings there instead of the row labels that is one of my pet peeves with pivot tables but a fast way to go.
Now the one downside here is if this data changes you're going to have to come back to the options tab click inside the pivot table let's just do it let's change that to 95% you have to click back inside the pivot table, go to the options tab click on refresh and let it refresh back in Excel 2003 it was a little bit different you would take the code value and drag it to column A take the percentage value drag it to column B and then you would look for that field settings icon that icon right there was on the floating pivot table toolbar choose that and click average and you would the same results although Excel 2003 data it's all the data menu data pivot table and finish would get you there as well.
All right Mike, let's see what you have.
Mike Girving: Thanks MrExcel.
Hey, we all know pivot tables rule and the reason they're so perfectly because what is the one-line description of what a pivot table does?
It does calculations with conditions or criteria here you know we can switch over to a formula.
But, it's going to involve an array formula and you have to get all fancy of it, here you just gotta drag and drop and change the calculations so what are we doing, we're calculating an average based on a particular item from this column in this case 102.
So, if we come over here if we're going to try and do this with formats i'm going to use advanced filter unique lists to extract exactly one instance of each item in column A. In 2003 the keyboard was ALT D, F, A and then you get this.
In 2007 or later, it's ALT A, Q.
Now you know when 2007 came out I remember going through all the ALT keyboards and I had this one memorized D, F, A and it makes sense Data, Filter, Advance.
So, it was easy to memorize.
But, this one is a lot cure and a lot shorter and A and Q are right next to each other so it turns out that this keyboard was pretty awesome ALT A, Q.
We get to advanced filter.
I'm going to say copy to another location it completely got the wrong list range.
I'm going to click in A1 you going to have field names when you're using advanced filter unique records only if you don't have a field named that'll be exactly one duplicate because it will treat the first item as a field name so I have cell A1 CTRL+SHIFT down arrow.
Criteria range you could put criteria using unique records only, but we're not going to have any copy too, I'm going to CTRL+Home and then click on D3 their CTRL+Home just jump me up to the top say I was down ten thousand records and this is what we want unique records only.
I'm going to click Boop and there we have a unique list so and that's a convenient way, if you do this a lot, especially with keyboards to get a unique list you don't have to use those crazy formulas and I'm going to say average that'll be the field names CTRL+Enter CTRL+B Enter.
Here we want to use the average know if you were in 2007 or later you just use average.
If, but, if you're in earlier versions that function didn't exist, meaning 2007 or later has this 2003 doesn't.
No problem, it's almost exactly the same as average if except for what?
Average?
And then you put the if function inside so you say IF.
Now remember what our goal the average function is want some number.
So, our goal is to get these numbers into the average.
But, with the if we can just give it a logical test and an array of trues and falses or an array calculation where we give it the whole column CTRL+SHIFT down arrow F4 to lock.
I'm gonna say is anything in that column equal to relative cell reference now that logical test is expecting a single true or false were given it an array operation there's an equal sign on an array of items so when we highlight and hit the F9 it'll spit out lots of answers trues and falses CTRL+Z because we're giving it an array operation this formula will require CTRL+SHIFT+ENTER that argument logical test it doesn't matter what other function if sits in because this is a special array operation we're going to have to use CTRL+SHIFT+ENTER.
Now we have all those trues and falses.
Hey, what's the goal is to put these numbers into the average function CTRL+SHIFT+DOWN ARROW F4 to lock it, we can leave the false out because what's cool about that is the if function will automatically put falses in so if I were to highlight this and hit the F9 we have filtered those values in column B using falses.
So, now we only have the actual values that are associated with the 101.
Now CTRL+ Z we're just using F9 and CTRL+Z to see how the formula is working and how it's created.
I'm going to close parenthesis and if you hit enter, it'll give you sometimes a value by implicit intersection or in this case nothing you've got to enter this special formula with a special keystroke that says hey Excel we're making an array calculation hold CTRL+SHIFT and ENTER.
You could see up in the formula bar those curly brackets, that's Excel says, hey!
I understood that you're making an array calculation.
So, now i can go double click and send it down and boom and the thing about this is if I change the calculation here it instantly updates so when i hit enter you could see that instantly updates and that's really one big advantage to formulas over pivot tables.
However, that's a lot more complicated than dragging and dropping in a pivot table all right throw back to MrExcel.
Bill Jelen: All right, Mike that was a cool array formula =AVERAGE (IF (. But you know I always said AVERAGE IF when that came along and Excel 2007 nice and that's just for people, can't figure out how to sum if count if.
So, another way to go if you don't want to use CTRL+SHIFT+ENTER =SUMIF I look through this range over here in column A F4 for countless see if it's equal to 101.
If it is, add up the corresponding range from B and actually you don't have to select the whole range just the first cell will do it some if /COUNTIF here's our range same thing over at a CTRL+SHIFT+DOWN ARROW F4 comma see if it's equal to 1 0 1 and we should get the exact same result with just a regular enter and no CTRL+SHIFT+ENTER much longer formula but hey it's just a sum / account although in this case some if / COUNTIF a couple of great ways to go there.
I want to thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin ExcelIsFun.
This is our episode 142: Average of Column B for each Unique Value in Column A.
Bill Jellen: All right Mike we have a great question 7 by Sean at YouTube.
He has a very large table, much larger than this table, there's codes in column A and then percentages in column B and for each unique code in column A he needs to get the average of the percentages over in B. He tried using an index and match, but it returns only the top value.
Also tried nesting an if statement but ran into a problem with 7F statements that tells me that he is in Excel 2003 or earlier otherwise we could go to 32 if statements.
But, if statements aren't the way to solve this I have lots of different ideas in mind, but I'm going to go to the old tried-and-true pivot table.
First, so we have one seldom data selected insert pivot table, existing worksheet, click next to our data click OK.
Checkmark code which will send it to the wrong spot I'm going to undo that instead of a check mark I’m going to drag code down to row labels and then I want to check mark percentages which is giving me a sum of percentage by default but we can change that click right there on the first time of percentage field settings change to average click OK.
We're really done at this point.
I'm going to go back to design change the report layout in tabular form to get real headings there instead of the row labels that is one of my pet peeves with pivot tables but a fast way to go.
Now the one downside here is if this data changes you're going to have to come back to the options tab click inside the pivot table let's just do it let's change that to 95% you have to click back inside the pivot table, go to the options tab click on refresh and let it refresh back in Excel 2003 it was a little bit different you would take the code value and drag it to column A take the percentage value drag it to column B and then you would look for that field settings icon that icon right there was on the floating pivot table toolbar choose that and click average and you would the same results although Excel 2003 data it's all the data menu data pivot table and finish would get you there as well.
All right Mike, let's see what you have.
Mike Girving: Thanks MrExcel.
Hey, we all know pivot tables rule and the reason they're so perfectly because what is the one-line description of what a pivot table does?
It does calculations with conditions or criteria here you know we can switch over to a formula.
But, it's going to involve an array formula and you have to get all fancy of it, here you just gotta drag and drop and change the calculations so what are we doing, we're calculating an average based on a particular item from this column in this case 102.
So, if we come over here if we're going to try and do this with formats i'm going to use advanced filter unique lists to extract exactly one instance of each item in column A. In 2003 the keyboard was ALT D, F, A and then you get this.
In 2007 or later, it's ALT A, Q.
Now you know when 2007 came out I remember going through all the ALT keyboards and I had this one memorized D, F, A and it makes sense Data, Filter, Advance.
So, it was easy to memorize.
But, this one is a lot cure and a lot shorter and A and Q are right next to each other so it turns out that this keyboard was pretty awesome ALT A, Q.
We get to advanced filter.
I'm going to say copy to another location it completely got the wrong list range.
I'm going to click in A1 you going to have field names when you're using advanced filter unique records only if you don't have a field named that'll be exactly one duplicate because it will treat the first item as a field name so I have cell A1 CTRL+SHIFT down arrow.
Criteria range you could put criteria using unique records only, but we're not going to have any copy too, I'm going to CTRL+Home and then click on D3 their CTRL+Home just jump me up to the top say I was down ten thousand records and this is what we want unique records only.
I'm going to click Boop and there we have a unique list so and that's a convenient way, if you do this a lot, especially with keyboards to get a unique list you don't have to use those crazy formulas and I'm going to say average that'll be the field names CTRL+Enter CTRL+B Enter.
Here we want to use the average know if you were in 2007 or later you just use average.
If, but, if you're in earlier versions that function didn't exist, meaning 2007 or later has this 2003 doesn't.
No problem, it's almost exactly the same as average if except for what?
Average?
And then you put the if function inside so you say IF.
Now remember what our goal the average function is want some number.
So, our goal is to get these numbers into the average.
But, with the if we can just give it a logical test and an array of trues and falses or an array calculation where we give it the whole column CTRL+SHIFT down arrow F4 to lock.
I'm gonna say is anything in that column equal to relative cell reference now that logical test is expecting a single true or false were given it an array operation there's an equal sign on an array of items so when we highlight and hit the F9 it'll spit out lots of answers trues and falses CTRL+Z because we're giving it an array operation this formula will require CTRL+SHIFT+ENTER that argument logical test it doesn't matter what other function if sits in because this is a special array operation we're going to have to use CTRL+SHIFT+ENTER.
Now we have all those trues and falses.
Hey, what's the goal is to put these numbers into the average function CTRL+SHIFT+DOWN ARROW F4 to lock it, we can leave the false out because what's cool about that is the if function will automatically put falses in so if I were to highlight this and hit the F9 we have filtered those values in column B using falses.
So, now we only have the actual values that are associated with the 101.
Now CTRL+ Z we're just using F9 and CTRL+Z to see how the formula is working and how it's created.
I'm going to close parenthesis and if you hit enter, it'll give you sometimes a value by implicit intersection or in this case nothing you've got to enter this special formula with a special keystroke that says hey Excel we're making an array calculation hold CTRL+SHIFT and ENTER.
You could see up in the formula bar those curly brackets, that's Excel says, hey!
I understood that you're making an array calculation.
So, now i can go double click and send it down and boom and the thing about this is if I change the calculation here it instantly updates so when i hit enter you could see that instantly updates and that's really one big advantage to formulas over pivot tables.
However, that's a lot more complicated than dragging and dropping in a pivot table all right throw back to MrExcel.
Bill Jelen: All right, Mike that was a cool array formula =AVERAGE (IF (. But you know I always said AVERAGE IF when that came along and Excel 2007 nice and that's just for people, can't figure out how to sum if count if.
So, another way to go if you don't want to use CTRL+SHIFT+ENTER =SUMIF I look through this range over here in column A F4 for countless see if it's equal to 101.
If it is, add up the corresponding range from B and actually you don't have to select the whole range just the first cell will do it some if /COUNTIF here's our range same thing over at a CTRL+SHIFT+DOWN ARROW F4 comma see if it's equal to 1 0 1 and we should get the exact same result with just a regular enter and no CTRL+SHIFT+ENTER much longer formula but hey it's just a sum / account although in this case some if / COUNTIF a couple of great ways to go there.
I want to thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and ExcelIsFun.
