Taylor has to find Averages within Ranges of the Data Set. Bill 'MrExcel' Jelen immediately sets up a Pivot Table - Mike 'ExcelisFun' Girvin chooses to go with a different method using =AVERAGEIF and more.
Mike and Bill also encourage us to check out SpeedTools Add-in from Excel and UDF Performance Stuff featuring the work of Charles Williams -'The Master of Fast'- with dozens of functions including Fast, Efficient VLOOKUPs. Follow along with Episode #1657 to solve the Average question and to learn more about the New Add-in from Charles Williams.
Mike and Bill also encourage us to check out SpeedTools Add-in from Excel and UDF Performance Stuff featuring the work of Charles Williams -'The Master of Fast'- with dozens of functions including Fast, Efficient VLOOKUPs. Follow along with Episode #1657 to solve the Average question and to learn more about the New Add-in from Charles Williams.
Transcript of the video:
Hey welcome back, it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel.
and I'll be joined by Mike Girvin: 'Excel is Fun' Bill: Mike Girvin from Excel.
This is our Episode 114; Get some Averages for a Set of Numbers All right so, our question sent in today by Taylor.
Taylor has data like this Male 1, Male 2, Female 1, Female 2, and some numbers we wanna come up with an Average, for each of these categories but to make it even more difficult.
We're looking for ranges, so all the numbers of fall between 0 and 9, what are the average of those for this category?
So I am gonna do this with a pivot table.
So Insert, Pivot Table, OK.
And we're gonna put Category across the top, so there's our Female 2, Male 2, Male 1, Female 1 and then down the sign, we'll put numbers.
Check this out, numbers down the side.
That's crazy, why would anyone do that?
Because I'm gonna use the Group Field feature, a group from 1 to 29, in groups of 10.
Click OK, so I guess we want 1 - 10, 11 to 20, 21 to 30.
All right now, that I have that, I wanna take numbers and drag it down to the values area and they're gonna give me a Count.
Isn't that annoying?
We're gonna come here to Field Settings, change it to Average and I'm gonna click Number Format and figure out how many decimal places i want?
Maybe just 1 would be fine.
Click OK, click OK and we now have 4: 11 to 20, Female 2, the Average is 15, Average overall for female 2 is 11.6.
Average overall for people in the 11 to 20 range is 14.4, so those all kind of look right.
Let's take a quick look here, just do a Home, Conditional Formatting, Color Scale: Green to Red, so as we really expect the numbers down, at 21 to 30 have larger averages and the numbers appear and 1 to 10.
All right, Mike lets see what you can do?
Mike: MrExcel! Now I know why they're calling MrExcel coz if I'm gonna do with this with a formula, My heavens! The inside of here will be some complicated formula, where I have to use Mixed Cell References and then this 2, Pivot Tables, they do rule.
So in order to do this with a formula, we're gonna need some Inputs, right!
Greater than or equal to 0 less than or equal to 9.
So I'm gonna build a little table like this.
Actually, I could probably zoom in like this and 2007 or later, you got to be kidding me.
The AVERAGEIF within S, there's how many criteria, there's 1, 2, 3, so the Average range that's the Numbers, Ctrl+Shift+down arrow+F, define names, I haven't defined name there.
So those are the numbers, comma and then the rest of this is Criteria.
Criteria range and Criteria.
So the criteria range, Ctrl+Shift+down arrow+comma and then the criteria.
Well, I have to click on here and as I copy it down, I need it to be locked on this Male but when I move this over, it needs to move to Male 2, so I'm gonna hit the F4 key to lock the row but not the column, comma, criteria range.
These are gonna be the numbers again coz I have 1, 2 conditions, comma, the first one I'm gonna say greater than or equal to in double quotes and then join it using the ampersand, Shift+7 and that number right there has to be locked, when I go this way but allowed to move when I copy down, so I'm going to hit the F4 key 1, 2, 3 times, lock the D but not the 11.
I get these numbers again, comma and then this one's gonna be in double quotes, less than or equal to and double quotes and join it to that 9, 1, 2, 3 times and that should do it.
Ctrl+ Enter and then I Copy it down and over.
Oh look at this, I'm already, the Pivot Table, I think just gave it a blank, right.
So now I'm gonna have to since there are no Male ones in between here.
There's no numbers, I'm gonna have to come here and do 2007 and later the IFERROR.
Remember in earlier versions 2003 and before, IF we had, we have to do an array formula with average and a bunch of IFS and stuff and we'd have to list it twice but IFERROR no way.
I just have a thing there in the value that either gets me what I want or an error. So if it's an error that IFERROR knows.
I'm gonna put a double quote.
Sorry double-quote, double-quote a null text string, which says hey show nothing.
Ctrl+Enter+double click and send it down copy it over and then I'm not gonna get as fancy, I'll just leave it there.
Bill: All right, that's cool, that's good for that new SUMIFS.
Nice improvement much better than SUMIFS.
[ lets us handle a lot of ] and avoid the whole SUM product thing, right.
Mike: Oh yeah.
Bill: Yes and bro.
Mike: But I think I used AVERAGEIFS Bill: AVERAGEIFS, SUMIFS, AVERAGEIFS, COUNTIFS all those.
Right, I kind of made fun of AVERAGEIFS because I said 'hey if we could just do SUMIFS / COUNTIFS, you would have AVERAGEIFS.
This is like making it too easy for people but I guess that's just me being me.
Mike: Well the except for the AVERAGEIFS, SUMIFS, COUNTIFS, all they calculates so much faster.
Bill: It's true.
Mike: then those SUM products.
Bill: All right, there you go.
Mike: Who is it? Who's the master of fast?
Charles Williams Bill: Yes Mike: Yeah that famous white paper article a bunch of years ago and he basically said if you can use the SUMIFS, AVERAGEIFS, you got to use them.
Bill: Charles was supposed to do a session at the MVP summit today but he wasn't able to come but there's presentation anyway.
He has a great Add-In that has new vlookups, like better vlookups all kinds like 90 new functions that is Add-In.
It's pretty wild, we saw some of those today.
I don't know if he has anything for this but yeah.
Mike: What made the name of Adam?
Bill: That would be a great thing and we'll have to put that as a call out over the...
When we ended it because I can't remember right now but it was like Mike: cool. I can't wait to get that Add-In.
Bill: All kinds of new vlookups will have to teach Excel as fire.
How to do the new advanced vlookups.
Mike: He'll be teaching us in few -- Bill: I'm sure he will.
Hey, I wanna thank you for stopping by. See you next week for another dueling Excel netcast from MrExcel.
Bill: and Mike: Excel is Fun.
I'm Bill Jelen from MrExcel.
and I'll be joined by Mike Girvin: 'Excel is Fun' Bill: Mike Girvin from Excel.
This is our Episode 114; Get some Averages for a Set of Numbers All right so, our question sent in today by Taylor.
Taylor has data like this Male 1, Male 2, Female 1, Female 2, and some numbers we wanna come up with an Average, for each of these categories but to make it even more difficult.
We're looking for ranges, so all the numbers of fall between 0 and 9, what are the average of those for this category?
So I am gonna do this with a pivot table.
So Insert, Pivot Table, OK.
And we're gonna put Category across the top, so there's our Female 2, Male 2, Male 1, Female 1 and then down the sign, we'll put numbers.
Check this out, numbers down the side.
That's crazy, why would anyone do that?
Because I'm gonna use the Group Field feature, a group from 1 to 29, in groups of 10.
Click OK, so I guess we want 1 - 10, 11 to 20, 21 to 30.
All right now, that I have that, I wanna take numbers and drag it down to the values area and they're gonna give me a Count.
Isn't that annoying?
We're gonna come here to Field Settings, change it to Average and I'm gonna click Number Format and figure out how many decimal places i want?
Maybe just 1 would be fine.
Click OK, click OK and we now have 4: 11 to 20, Female 2, the Average is 15, Average overall for female 2 is 11.6.
Average overall for people in the 11 to 20 range is 14.4, so those all kind of look right.
Let's take a quick look here, just do a Home, Conditional Formatting, Color Scale: Green to Red, so as we really expect the numbers down, at 21 to 30 have larger averages and the numbers appear and 1 to 10.
All right, Mike lets see what you can do?
Mike: MrExcel! Now I know why they're calling MrExcel coz if I'm gonna do with this with a formula, My heavens! The inside of here will be some complicated formula, where I have to use Mixed Cell References and then this 2, Pivot Tables, they do rule.
So in order to do this with a formula, we're gonna need some Inputs, right!
Greater than or equal to 0 less than or equal to 9.
So I'm gonna build a little table like this.
Actually, I could probably zoom in like this and 2007 or later, you got to be kidding me.
The AVERAGEIF within S, there's how many criteria, there's 1, 2, 3, so the Average range that's the Numbers, Ctrl+Shift+down arrow+F, define names, I haven't defined name there.
So those are the numbers, comma and then the rest of this is Criteria.
Criteria range and Criteria.
So the criteria range, Ctrl+Shift+down arrow+comma and then the criteria.
Well, I have to click on here and as I copy it down, I need it to be locked on this Male but when I move this over, it needs to move to Male 2, so I'm gonna hit the F4 key to lock the row but not the column, comma, criteria range.
These are gonna be the numbers again coz I have 1, 2 conditions, comma, the first one I'm gonna say greater than or equal to in double quotes and then join it using the ampersand, Shift+7 and that number right there has to be locked, when I go this way but allowed to move when I copy down, so I'm going to hit the F4 key 1, 2, 3 times, lock the D but not the 11.
I get these numbers again, comma and then this one's gonna be in double quotes, less than or equal to and double quotes and join it to that 9, 1, 2, 3 times and that should do it.
Ctrl+ Enter and then I Copy it down and over.
Oh look at this, I'm already, the Pivot Table, I think just gave it a blank, right.
So now I'm gonna have to since there are no Male ones in between here.
There's no numbers, I'm gonna have to come here and do 2007 and later the IFERROR.
Remember in earlier versions 2003 and before, IF we had, we have to do an array formula with average and a bunch of IFS and stuff and we'd have to list it twice but IFERROR no way.
I just have a thing there in the value that either gets me what I want or an error. So if it's an error that IFERROR knows.
I'm gonna put a double quote.
Sorry double-quote, double-quote a null text string, which says hey show nothing.
Ctrl+Enter+double click and send it down copy it over and then I'm not gonna get as fancy, I'll just leave it there.
Bill: All right, that's cool, that's good for that new SUMIFS.
Nice improvement much better than SUMIFS.
[ lets us handle a lot of ] and avoid the whole SUM product thing, right.
Mike: Oh yeah.
Bill: Yes and bro.
Mike: But I think I used AVERAGEIFS Bill: AVERAGEIFS, SUMIFS, AVERAGEIFS, COUNTIFS all those.
Right, I kind of made fun of AVERAGEIFS because I said 'hey if we could just do SUMIFS / COUNTIFS, you would have AVERAGEIFS.
This is like making it too easy for people but I guess that's just me being me.
Mike: Well the except for the AVERAGEIFS, SUMIFS, COUNTIFS, all they calculates so much faster.
Bill: It's true.
Mike: then those SUM products.
Bill: All right, there you go.
Mike: Who is it? Who's the master of fast?
Charles Williams Bill: Yes Mike: Yeah that famous white paper article a bunch of years ago and he basically said if you can use the SUMIFS, AVERAGEIFS, you got to use them.
Bill: Charles was supposed to do a session at the MVP summit today but he wasn't able to come but there's presentation anyway.
He has a great Add-In that has new vlookups, like better vlookups all kinds like 90 new functions that is Add-In.
It's pretty wild, we saw some of those today.
I don't know if he has anything for this but yeah.
Mike: What made the name of Adam?
Bill: That would be a great thing and we'll have to put that as a call out over the...
When we ended it because I can't remember right now but it was like Mike: cool. I can't wait to get that Add-In.
Bill: All kinds of new vlookups will have to teach Excel as fire.
How to do the new advanced vlookups.
Mike: He'll be teaching us in few -- Bill: I'm sure he will.
Hey, I wanna thank you for stopping by. See you next week for another dueling Excel netcast from MrExcel.
Bill: and Mike: Excel is Fun.
