How do you deal with many levels in an IF statement? Episode 768 discusses nesting IF statements.
This blog is the video 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 blog is the video 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.
Someone asked a question about using IF when we have multiple conditions and there's 2 different ways to do this.
We're going to cover 1 way today and then the other way tomorrow.
Typically, the IF function works out where we pass a logical test, something is going to be either true or false, and then what to do if it's true and what to do if it's false.
So, let’s say that we have a VP of Sales who comes up with a plan that says, hey, we're going to pay a 1% bonus any time you sell a deal larger than $20,000.
So, in that case, the logical test is F2 > 20,000, put a , and then what to do if it's true?
Well, if it's true, we're paying 0.01 times F2 and then a , and what to do if it's false?
Well, if it's false, then we pay 0.
Double click the fill handle and we'll copy that down, and you see that only the deals above 20,000 get a bonus.
[ =IF(F2>20000,0.01*F2,0) ] Okay. Well, now, what if there's a second option? A second option.
They say if it's > $20,000, we're going to pay a 1% bonus, but if it's > $15,000, then we're going to pay a quarter of a % bonus.
So, in this case, it's relatively simple in that we just replace this final 0 with a brand new IF statement.
So, IF F2 is > $15,000, then 0.0025 times F2, otherwise 0.
Make sure to put the ). Copy that down and you'll see that now, here in this cell, we're getting a quarter of a % bonus.
[ =IF(F2>20000,0.01*F2,IF(F2>15000,0.0025*F2,0)) ] Now, that's the common use for having to add more conditions.
The one “got you” that you have to be aware of is that you cannot nest more than 7 IF statements in a single formula.
In Excel 2007, you can go up to 32 but, frankly, I don't think you should do that.
If you get beyond 7, you really should switch over to VLOOKUP.
Now, in tomorrow's netcast, we're going to take a look at taking the original IF statement and changing it around using either an AND or an OR function in order to really have 2 conditions for that bonus to be paid.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Someone asked a question about using IF when we have multiple conditions and there's 2 different ways to do this.
We're going to cover 1 way today and then the other way tomorrow.
Typically, the IF function works out where we pass a logical test, something is going to be either true or false, and then what to do if it's true and what to do if it's false.
So, let’s say that we have a VP of Sales who comes up with a plan that says, hey, we're going to pay a 1% bonus any time you sell a deal larger than $20,000.
So, in that case, the logical test is F2 > 20,000, put a , and then what to do if it's true?
Well, if it's true, we're paying 0.01 times F2 and then a , and what to do if it's false?
Well, if it's false, then we pay 0.
Double click the fill handle and we'll copy that down, and you see that only the deals above 20,000 get a bonus.
[ =IF(F2>20000,0.01*F2,0) ] Okay. Well, now, what if there's a second option? A second option.
They say if it's > $20,000, we're going to pay a 1% bonus, but if it's > $15,000, then we're going to pay a quarter of a % bonus.
So, in this case, it's relatively simple in that we just replace this final 0 with a brand new IF statement.
So, IF F2 is > $15,000, then 0.0025 times F2, otherwise 0.
Make sure to put the ). Copy that down and you'll see that now, here in this cell, we're getting a quarter of a % bonus.
[ =IF(F2>20000,0.01*F2,IF(F2>15000,0.0025*F2,0)) ] Now, that's the common use for having to add more conditions.
The one “got you” that you have to be aware of is that you cannot nest more than 7 IF statements in a single formula.
In Excel 2007, you can go up to 32 but, frankly, I don't think you should do that.
If you get beyond 7, you really should switch over to VLOOKUP.
Now, in tomorrow's netcast, we're going to take a look at taking the original IF statement and changing it around using either an AND or an OR function in order to really have 2 conditions for that bonus to be paid.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
