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!
Today, Multiple Criteria Testing the easy way from JP Maltais! Find the Records that meet the given criteria; in Episode #1614 JP Maltais shows us how its done.
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!
Today, Multiple Criteria Testing the easy way from JP Maltais! Find the Records that meet the given criteria; in Episode #1614 JP Maltais shows us how its done.
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:
Bill Jelen: Hey, welcome back to MrExcel netcast, I'm Bill Jelen.
We are still here in New York City – these are amazing tricks out of the 16 brightest Excel rock stars in the world that qualified to come here for the ModelOff World Financial Competition.
Now we have J.P. Maltais with this cool trick for Multiple Criteria Testing.
All right so J.P. -- where are you from?
J.P. Maltais: I'm from Canada, originally from the French part of Quebec City.
Bill Jelen: French part of Quebec City.
How did you hear about the competition?
J.P. Maltais: I heard through the internet, in a friend’s website and I forgot exactly which -- but it sounded fun so I thought I would try it.
Bill Jelen: That's great.
Okay tell us what your trick is.
J.P. Maltais: This is a simple trick to test on a condition over several criteria -- and what I'll do is, I'll use conditional formatting along with the fact that Excel recognizes the numbers 1 and 0 as logical criteria to be able to test things really quickly and easily.
What I'll do is first of all, do a conditional formatting to be able to show the numbers 1 and 0 in a more user-friendly way.
So what I'll do is, I'll assign the value yes for any positive number and I'll assign the value no for anything which is 0 -- so if it's true, if it's number 1 that is true to be shown as a yes, this.
Bill Jelen: Ah, cool.
J.P. Maltais: So if it's, negative, if it's false, if it's 0 it will show as a no.
So what I've done here is a bit of small spreadsheet with just three very basic data sets – let’s call them Mark, Sam, and Mary.
And I'll say here that Mark and Mary are both happy, and I'll say that Mark is rich; Sam is rich but Mary isn't -- and I'll say that, you know what?
Mark and Sam aren’t in the best in the world, but Mary is.
And I will say, watch this, and SUM total would test as if, will say if they are doing well, “If doing well” right.
Bill Jelen: Okay.
J.P. Maltais: What I'll do is a little a small formula =IF, and I'll test if all of the conditions are met.
Before this all I have to do is multiply the numbers, one by the other.
So if the number, if the result is 1, if it's true then I'll say well -- all is well.
But if the answer is 0, this if it's false I will say well, better improve.
Bill Jelen: Okay.
J.P. Maltais: And that way I can test for the condition very, very easily.
in this case because, it is always a known somewhere everyone has to improve, let's make Mary very rich -- but if we make Mary rich then all is well.
So in this way you're able to test for conditional, editing a huge number of criteria’s.
I used this when I did an aircraft leasing transaction and I had a hundred planes with a number of conditions and with this we were able to test the condition, editing in each plane and it actually worked really well.
Bill Jelen: So you had 100 rows, hundred different planes, how many different criteria is going across in you?
J.P. Maltais: Exactly, I had 100 sheets.
Bill Jelen: 100 sheets, oh my gosh.
J.P. Maltais: Over like 25 years, it was very complex.
And in this way we were able to keep track of what was going on.
Bill Jelen: That's cool.
Okay so just to recap here -- we have the numbers 1 and 0, you have a custom number format, very cool there the first zone, is yes -- that's for positive numbers the second zone for negative numbers.
We don't have any negative number so that's blank, third zone four zeros, shows no.
So that allows us to see the words yes and no, which is good, but then it's actually storing the ones and zeros which allows this formula over here; what a great, great trick.
All right J.P. – thanks very much.
J.P. Maltais: Thank you very much.
Bill Jelen: All right, hey I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
We are still here in New York City – these are amazing tricks out of the 16 brightest Excel rock stars in the world that qualified to come here for the ModelOff World Financial Competition.
Now we have J.P. Maltais with this cool trick for Multiple Criteria Testing.
All right so J.P. -- where are you from?
J.P. Maltais: I'm from Canada, originally from the French part of Quebec City.
Bill Jelen: French part of Quebec City.
How did you hear about the competition?
J.P. Maltais: I heard through the internet, in a friend’s website and I forgot exactly which -- but it sounded fun so I thought I would try it.
Bill Jelen: That's great.
Okay tell us what your trick is.
J.P. Maltais: This is a simple trick to test on a condition over several criteria -- and what I'll do is, I'll use conditional formatting along with the fact that Excel recognizes the numbers 1 and 0 as logical criteria to be able to test things really quickly and easily.
What I'll do is first of all, do a conditional formatting to be able to show the numbers 1 and 0 in a more user-friendly way.
So what I'll do is, I'll assign the value yes for any positive number and I'll assign the value no for anything which is 0 -- so if it's true, if it's number 1 that is true to be shown as a yes, this.
Bill Jelen: Ah, cool.
J.P. Maltais: So if it's, negative, if it's false, if it's 0 it will show as a no.
So what I've done here is a bit of small spreadsheet with just three very basic data sets – let’s call them Mark, Sam, and Mary.
And I'll say here that Mark and Mary are both happy, and I'll say that Mark is rich; Sam is rich but Mary isn't -- and I'll say that, you know what?
Mark and Sam aren’t in the best in the world, but Mary is.
And I will say, watch this, and SUM total would test as if, will say if they are doing well, “If doing well” right.
Bill Jelen: Okay.
J.P. Maltais: What I'll do is a little a small formula =IF, and I'll test if all of the conditions are met.
Before this all I have to do is multiply the numbers, one by the other.
So if the number, if the result is 1, if it's true then I'll say well -- all is well.
But if the answer is 0, this if it's false I will say well, better improve.
Bill Jelen: Okay.
J.P. Maltais: And that way I can test for the condition very, very easily.
in this case because, it is always a known somewhere everyone has to improve, let's make Mary very rich -- but if we make Mary rich then all is well.
So in this way you're able to test for conditional, editing a huge number of criteria’s.
I used this when I did an aircraft leasing transaction and I had a hundred planes with a number of conditions and with this we were able to test the condition, editing in each plane and it actually worked really well.
Bill Jelen: So you had 100 rows, hundred different planes, how many different criteria is going across in you?
J.P. Maltais: Exactly, I had 100 sheets.
Bill Jelen: 100 sheets, oh my gosh.
J.P. Maltais: Over like 25 years, it was very complex.
And in this way we were able to keep track of what was going on.
Bill Jelen: That's cool.
Okay so just to recap here -- we have the numbers 1 and 0, you have a custom number format, very cool there the first zone, is yes -- that's for positive numbers the second zone for negative numbers.
We don't have any negative number so that's blank, third zone four zeros, shows no.
So that allows us to see the words yes and no, which is good, but then it's actually storing the ones and zeros which allows this formula over here; what a great, great trick.
All right J.P. – thanks very much.
J.P. Maltais: Thank you very much.
Bill Jelen: All right, hey I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
