Bill 'MrExcel' Jelen: [Almost] Live From New York City to judge the ModelOff Worldwide Excel Championship! With 16 Excel Rock Stars, from around the Globe, in NYC -- and Bill having access to them all -- why not do some Podcasting OnSite!
Bill is with Matijn Reekers for our last 'Almost Live from The ModelOff championships' series. Today, in Episode #1618, Bill and Matijn are looking at Summing and Averaging Multiple Criteria in Excel.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Bill is with Matijn Reekers for our last 'Almost Live from The ModelOff championships' series. Today, in Episode #1618, Bill and Matijn are looking at Summing and Averaging Multiple Criteria in Excel.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
We're still in New York City at the Model Off world competition finals.
Here we have Martijin again with SUM/Average Multiple Criteria.
This is a great trick watch how fast he uses the keyboard.
Martijin: One trick I've come across is, when you wanna use more than one criteria, you can't really use SUMIF or MEDIANIF.
Some of the other criteria formulas but some products actually solves this.
So what it does and I've got some Days here, this is just an array with Dates, Amounts and then Count, which I need to get the Average.
So, what some product does is if you put in a criteria, it looks at it as a factor and returns either a 1 or a 0.
So, the syntax here is quite important.
What you need to do is have 2 brackets, select your Date range and say it Prints, this is larger than my first day.
So, this is gonna lookup, this value D2.
D2 in this array and it's gonna see if it's larger, if it's larger it's gonna return in 1, if it's not large it's gonna return in 0.
Okay, you need to multiply this by your second criteria.
It's very similar, it's gonna same array, should have hit before.
It's smaller than your second date and this, you multiply by the Amounts, Strangers attack!
I'll need to close some product from 1.
This gives you SUM: 522.
Let me fix this...
So, this just simply SUMs all the amounts.
If you wanna get the Average, which is much more insightful, what I do is, just add another column with counts just once, like Sex and Formula.
So, this is just gonna SUM number...
Yeah, we count this one, Bill: How'd you get that formula over there?
Did you copy and paste somehow?
Martijin: Oh no! Just hit Ctrl+R.
Bill: Ctrl+R to move to the right, hey that's a hot tip right there, Okay!
Martijin: Ctrl+R and you know, I fixed all the formulas, the only one I have to change, you know, I could have made that a Relative Reference.
I didn't just decide, Just move down.
Bill: I'll just note that you touch the mouse there, which is a cardinal sin, but okay!
Martijin: Well, I mean you should, I don't like using the mouse, but this is actually one of the occasion, where the mouse is pretty useful.
If you've got -- you open your formula and you just wanna move the entire array, it's actually quite useful to use mouse and I think it's fast than using keyboard typing, Bill: Okay that's good, now this probably the model off World Finals, are the only place where we could get into this esoteric discussion about the SUM product, they help file for some products is that you have to separate each of those by a comma but here you're using a multiplication sign and I understand why you're doing it.
That's the right way to do it at least in my book.
There's a lot of people though who put commas and then put minus in between each one.
But I think the multiplication is the hot way to go.
The multiplication actually-- Martijin: Yeah I mean! it does something else right, if you use multiplication it looks that...
the criteria factor which is what you need.
Bill: Right it changes into binary -- changes it from True/False to actual the 0's and 1's.
So, using the multiplication is the way that I like to go.
So, I'm gonna give you a point for that because that was the hot hot...
All right, yeah so hey check out the Model Off World Finals.
You can enter next year for 2013.
First two rounds are at your desk and then if you do well, you went to trips in New York City.
It's an amazing amazing competition.
All right, see you next time for another net cast from MrExcel.
We're still in New York City at the Model Off world competition finals.
Here we have Martijin again with SUM/Average Multiple Criteria.
This is a great trick watch how fast he uses the keyboard.
Martijin: One trick I've come across is, when you wanna use more than one criteria, you can't really use SUMIF or MEDIANIF.
Some of the other criteria formulas but some products actually solves this.
So what it does and I've got some Days here, this is just an array with Dates, Amounts and then Count, which I need to get the Average.
So, what some product does is if you put in a criteria, it looks at it as a factor and returns either a 1 or a 0.
So, the syntax here is quite important.
What you need to do is have 2 brackets, select your Date range and say it Prints, this is larger than my first day.
So, this is gonna lookup, this value D2.
D2 in this array and it's gonna see if it's larger, if it's larger it's gonna return in 1, if it's not large it's gonna return in 0.
Okay, you need to multiply this by your second criteria.
It's very similar, it's gonna same array, should have hit before.
It's smaller than your second date and this, you multiply by the Amounts, Strangers attack!
I'll need to close some product from 1.
This gives you SUM: 522.
Let me fix this...
So, this just simply SUMs all the amounts.
If you wanna get the Average, which is much more insightful, what I do is, just add another column with counts just once, like Sex and Formula.
So, this is just gonna SUM number...
Yeah, we count this one, Bill: How'd you get that formula over there?
Did you copy and paste somehow?
Martijin: Oh no! Just hit Ctrl+R.
Bill: Ctrl+R to move to the right, hey that's a hot tip right there, Okay!
Martijin: Ctrl+R and you know, I fixed all the formulas, the only one I have to change, you know, I could have made that a Relative Reference.
I didn't just decide, Just move down.
Bill: I'll just note that you touch the mouse there, which is a cardinal sin, but okay!
Martijin: Well, I mean you should, I don't like using the mouse, but this is actually one of the occasion, where the mouse is pretty useful.
If you've got -- you open your formula and you just wanna move the entire array, it's actually quite useful to use mouse and I think it's fast than using keyboard typing, Bill: Okay that's good, now this probably the model off World Finals, are the only place where we could get into this esoteric discussion about the SUM product, they help file for some products is that you have to separate each of those by a comma but here you're using a multiplication sign and I understand why you're doing it.
That's the right way to do it at least in my book.
There's a lot of people though who put commas and then put minus in between each one.
But I think the multiplication is the hot way to go.
The multiplication actually-- Martijin: Yeah I mean! it does something else right, if you use multiplication it looks that...
the criteria factor which is what you need.
Bill: Right it changes into binary -- changes it from True/False to actual the 0's and 1's.
So, using the multiplication is the way that I like to go.
So, I'm gonna give you a point for that because that was the hot hot...
All right, yeah so hey check out the Model Off World Finals.
You can enter next year for 2013.
First two rounds are at your desk and then if you do well, you went to trips in New York City.
It's an amazing amazing competition.
All right, see you next time for another net cast from MrExcel.
