Three ways to handle multiple conditions in an IF function. Episode recap: The simplest IF function is =IF(Logical Test,Formula if True, Formula if False)
But what to you do if you have to test two conditions?
Many people will do =IF(Test 1, IF(Test 2, Formula if True, False), False)
This gets unwieldy if 3, 5, 17 conditions!
Instead, use =IF(AND(t1,t2,t3,t4),Formula if True, Formula if False)
If you like AND, consider OR, NOT for other situations
NAND can be done with NOT(AND())
NOR can be done with NOT(OR())
Be careful when using XOR as the results are not what you expect
But what to you do if you have to test two conditions?
Many people will do =IF(Test 1, IF(Test 2, Formula if True, False), False)
This gets unwieldy if 3, 5, 17 conditions!
Instead, use =IF(AND(t1,t2,t3,t4),Formula if True, Formula if False)
If you like AND, consider OR, NOT for other situations
NAND can be done with NOT(AND())
NOR can be done with NOT(OR())
Be careful when using XOR as the results are not what you expect
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2025 - Multiple Conditions in IF!
I'll be podcasting this entire book, the “i” on the top-right hand corner will take you to the playlist for all of those podcasts!
Alright, we're going to start off with the world's most simple case of IF, the fictitious VP of Sales comes to us and says “Hey, this month anyone who has more than $20000 in sales gets a 2% bonus.” Alright, so the IF function has three parts: A logical test that's going out to say “Is B4>$20000?” Comma, then what to do if it's TRUE?
If it's TRUE, .02*B4, comma, what's do if it's FALSE?
Well, if you haven’t earned $20000, no bonus for you, is zero, alright.
Ctrl+Enter to copy that down, and you see that we have the bonus only on rows that were above $20000, this one close, but still no bonus, alright.
Now, there's never been a bonus plan this simple, right, there's always multiple rules, so here, we have to check to see if the revenue >$20000 AND if the gross profit percent >50%.
Alright, and if you know how to solve this, think about how you're going to solve this, alright, and I'm going to predict that a lot of you are saying “Well, we're going to start off with one IF statement, and then later on have another IF statement like this, the nested IFs.” And then there's a few of you who are saying “Well, let's do an IF statement, and then immediately right here inside of parentheses we go into another function called AND.” And then, there's a way to do this with no IFs at all.
Alright, so I'll take a look at it through those, so here are the first, the most common way, the nested IF, OK, here's how most people would do this.
=IF, do the first test, see if revenue in B4>$20000, if it is then do another IF, see if the gross profit percent >.05?
If that's TRUE, then both conditions are TRUE, we can do .02*B4, otherwise no bonus.
OK, but we're not done, close the inside parentheses, comma, and then if the first test was not true, no bonus, Ctrl+Enter to copy down.
You see that only the rows that are above $20000 and above the gross profit percent of 50% get the bonus.
Alright now, back in the day, there used to be this horrible limit, where you could not nest more than 7 IF statements.
That was a painful day, particularly if you were just slowly adding conditions over the months, and you finally had one that had 7 and you needed to add an 8th.
Alright, well today you can go to 32, I don't think you should ever go to 32, but if you just desperately need to go from 7 to 8, then this is a good thing, alright.
So this is the nested IF statement approach, when I do my live seminars, about half the room is doing this, but there's a much, much better way to go.
=IF and then immediately go into a function called AND, so inside the AND we put all the tests: Is the revenue >20000, comma, is a gross profit percent >.5.
If there were more tests, keep putting commas with the additional tests, and then close the end, everything in the end has to be TRUE for the end to be TRUE.
So if we get to this point if the end is TRUE, .02*revenue, otherwise 0, it's a shorter formula, it's easier to enter, you get the same results, life is great.
Alright, out of all the seminars I've done over the last 15 years, only once has someone walked in and hit me with this crazy formula.
She said “Look, we're just going to do =.02*the revenue, like that.” Alright, calculate the bonus, I’m like “Whoa, hang on, that’s going to be expensive, you're going to give the bonus to everyone.” she's like “Wait, I wasn't done, times, and then in parentheses we're going to put each condition, so revenue >20000 times, in parentheses gross profit percent >.5.” Alright, and here's what happens is we calculate the bonus, and then these evaluate to either TRUE or FALSE.
And when we force Excel to multiply a TRUE or FALSE times a number, the TRUE becomes 1, anything *1 is itself, the FALSE becomes 0! Alright, so what we have here is 2%*the revenue*1*0, anything*0=0, right, so that clears the bonus out.
This is Boolean logic, TRUE*TRUE, 1*1=1, if either them are FALSE or all of them are FALSE, that's going to evaluate to 0, and we get the exact same result.
Do I think you should switch over to this one?
No, it's confusing, unless you're leaving your job next week and you hate your coworkers, then feel free to switch over to this, alright.
If you like the AND function, there are other functions or checks to see if any of the conditions are TRUE, so this or this or this, it'll return TRUE.
NOT is going to reverse TRUE to FALSE and FALSE to TRUE, which is useful when you're trying to do the Boolean concepts of NAND or NOR.
NAND stands for not-and, it's TRUE when at least one condition is FALSE, alright.
So if none of them are TRUE, that's great, if a few of them are TRUE, that's great, but as soon as all of them are TRUE, then we don't pay.
NOR stands for not-or, it means that none of the conditions are TRUE - if this happens, or this happens, or this happens, no bonus for you.
And then XOR, now be careful with this one, it was introduced in Excel 2013, and it doesn't do what us as accountants think it should do.
Exclusive-or means that only one of the tests is TRUE, and it works when there's two conditions.
But for electrical engineers they are doing this in pair, so it doesn't give the results that you might think.
Alright, so here's Test 1, Test 2, Test 3, Test 4, three of them are TRUE, and XOR says “Is exactly one of these TRUE?” And when we do this XOR, it says “Yes, exactly one of those that's TRUE.” and that's because the Excel function is duplicating the operation of a very common chip that's used in electrical engineering, I know, it's shocking, right?
You think that Excel is only for accountants, but engineers also use Excel as well, and they apparently added XOR for them and not for accountants.
So the way that this is evaluated, as they look at the first two, “Is one of these 2 TRUE?
Yes!” Alright, so we get that TRUE, and then they take the answer from the XOR of this and compare it to the TRUE, “Is one of these 2 TRUE?
No, 2 of them are TRUE, so that becomes a FALSE!” Then they take that answer and XOR it with the last one, so they're doing this in pairs, right?
“Is one of these 2 TRUE?
Yes!” Alright, so that's how we get it.
It turns out that what it's actually doing, the electrical engineering thing is this counting if an odd number of inputs are TRUE.
Not necessarily useful for accountants, who are expecting it to do what XOR in English means.
Alright, lots of great tips in this book, useful stuff, and even this horrible discussion about NAND and XOR and things like that.
Buy the book, you'll have all these tips in the palm of your hand.
Recap from today: Simplest IF function, =IF logical test, what's to do if it's TRUE, what to do if it's FALSE, but if you have 2 conditions, a lot of people nest the IF statements, but just imagine if you had 3, 5, or 17 conditions to nest.
AND will solve that, a little shorten it, so if you like AND, there's also OR or NOT, you can do NAND, you can do NOR, but be careful when using that new Excel 2013 XOR, the results may not be what you expect.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting this entire book, the “i” on the top-right hand corner will take you to the playlist for all of those podcasts!
Alright, we're going to start off with the world's most simple case of IF, the fictitious VP of Sales comes to us and says “Hey, this month anyone who has more than $20000 in sales gets a 2% bonus.” Alright, so the IF function has three parts: A logical test that's going out to say “Is B4>$20000?” Comma, then what to do if it's TRUE?
If it's TRUE, .02*B4, comma, what's do if it's FALSE?
Well, if you haven’t earned $20000, no bonus for you, is zero, alright.
Ctrl+Enter to copy that down, and you see that we have the bonus only on rows that were above $20000, this one close, but still no bonus, alright.
Now, there's never been a bonus plan this simple, right, there's always multiple rules, so here, we have to check to see if the revenue >$20000 AND if the gross profit percent >50%.
Alright, and if you know how to solve this, think about how you're going to solve this, alright, and I'm going to predict that a lot of you are saying “Well, we're going to start off with one IF statement, and then later on have another IF statement like this, the nested IFs.” And then there's a few of you who are saying “Well, let's do an IF statement, and then immediately right here inside of parentheses we go into another function called AND.” And then, there's a way to do this with no IFs at all.
Alright, so I'll take a look at it through those, so here are the first, the most common way, the nested IF, OK, here's how most people would do this.
=IF, do the first test, see if revenue in B4>$20000, if it is then do another IF, see if the gross profit percent >.05?
If that's TRUE, then both conditions are TRUE, we can do .02*B4, otherwise no bonus.
OK, but we're not done, close the inside parentheses, comma, and then if the first test was not true, no bonus, Ctrl+Enter to copy down.
You see that only the rows that are above $20000 and above the gross profit percent of 50% get the bonus.
Alright now, back in the day, there used to be this horrible limit, where you could not nest more than 7 IF statements.
That was a painful day, particularly if you were just slowly adding conditions over the months, and you finally had one that had 7 and you needed to add an 8th.
Alright, well today you can go to 32, I don't think you should ever go to 32, but if you just desperately need to go from 7 to 8, then this is a good thing, alright.
So this is the nested IF statement approach, when I do my live seminars, about half the room is doing this, but there's a much, much better way to go.
=IF and then immediately go into a function called AND, so inside the AND we put all the tests: Is the revenue >20000, comma, is a gross profit percent >.5.
If there were more tests, keep putting commas with the additional tests, and then close the end, everything in the end has to be TRUE for the end to be TRUE.
So if we get to this point if the end is TRUE, .02*revenue, otherwise 0, it's a shorter formula, it's easier to enter, you get the same results, life is great.
Alright, out of all the seminars I've done over the last 15 years, only once has someone walked in and hit me with this crazy formula.
She said “Look, we're just going to do =.02*the revenue, like that.” Alright, calculate the bonus, I’m like “Whoa, hang on, that’s going to be expensive, you're going to give the bonus to everyone.” she's like “Wait, I wasn't done, times, and then in parentheses we're going to put each condition, so revenue >20000 times, in parentheses gross profit percent >.5.” Alright, and here's what happens is we calculate the bonus, and then these evaluate to either TRUE or FALSE.
And when we force Excel to multiply a TRUE or FALSE times a number, the TRUE becomes 1, anything *1 is itself, the FALSE becomes 0! Alright, so what we have here is 2%*the revenue*1*0, anything*0=0, right, so that clears the bonus out.
This is Boolean logic, TRUE*TRUE, 1*1=1, if either them are FALSE or all of them are FALSE, that's going to evaluate to 0, and we get the exact same result.
Do I think you should switch over to this one?
No, it's confusing, unless you're leaving your job next week and you hate your coworkers, then feel free to switch over to this, alright.
If you like the AND function, there are other functions or checks to see if any of the conditions are TRUE, so this or this or this, it'll return TRUE.
NOT is going to reverse TRUE to FALSE and FALSE to TRUE, which is useful when you're trying to do the Boolean concepts of NAND or NOR.
NAND stands for not-and, it's TRUE when at least one condition is FALSE, alright.
So if none of them are TRUE, that's great, if a few of them are TRUE, that's great, but as soon as all of them are TRUE, then we don't pay.
NOR stands for not-or, it means that none of the conditions are TRUE - if this happens, or this happens, or this happens, no bonus for you.
And then XOR, now be careful with this one, it was introduced in Excel 2013, and it doesn't do what us as accountants think it should do.
Exclusive-or means that only one of the tests is TRUE, and it works when there's two conditions.
But for electrical engineers they are doing this in pair, so it doesn't give the results that you might think.
Alright, so here's Test 1, Test 2, Test 3, Test 4, three of them are TRUE, and XOR says “Is exactly one of these TRUE?” And when we do this XOR, it says “Yes, exactly one of those that's TRUE.” and that's because the Excel function is duplicating the operation of a very common chip that's used in electrical engineering, I know, it's shocking, right?
You think that Excel is only for accountants, but engineers also use Excel as well, and they apparently added XOR for them and not for accountants.
So the way that this is evaluated, as they look at the first two, “Is one of these 2 TRUE?
Yes!” Alright, so we get that TRUE, and then they take the answer from the XOR of this and compare it to the TRUE, “Is one of these 2 TRUE?
No, 2 of them are TRUE, so that becomes a FALSE!” Then they take that answer and XOR it with the last one, so they're doing this in pairs, right?
“Is one of these 2 TRUE?
Yes!” Alright, so that's how we get it.
It turns out that what it's actually doing, the electrical engineering thing is this counting if an odd number of inputs are TRUE.
Not necessarily useful for accountants, who are expecting it to do what XOR in English means.
Alright, lots of great tips in this book, useful stuff, and even this horrible discussion about NAND and XOR and things like that.
Buy the book, you'll have all these tips in the palm of your hand.
Recap from today: Simplest IF function, =IF logical test, what's to do if it's TRUE, what to do if it's FALSE, but if you have 2 conditions, a lot of people nest the IF statements, but just imagine if you had 3, 5, or 17 conditions to nest.
AND will solve that, a little shorten it, so if you like AND, there's also OR or NOT, you can do NAND, you can do NOR, but be careful when using that new Excel 2013 XOR, the results may not be what you expect.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
