Episode 769 looks at the difficult variations on yesterday's podcast; how do you set up an IF statement that will only be true if two conditions are true? This episode will look at using OR, AND, NOT within the logical test of the IF function.
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.
Well, now, yesterday, I said that we had someone who asked about using an IF function with two conditions and I really went down the wrong path here.
I explained how to have two different IF statements rolled in together.
That's probably really not what they were asking for.
What they probably wanted to do is set up an IF statement where we pay the bonus only if two different conditions are met.
So, yesterday, we said the bonus is paid anytime the revenue is > $20,000.
Maybe we also need a second condition that the profit has to be > $10,000.
Well, to do two conditions is pretty tough here in this logical test.
There's no way to really put an AND statement or, you know, to indicate the two things need to be joined by an AND.
So, we have to use another function.
It's called the AND function, put in (, and now, in commas, we can put as many conditions that we want inside the AND function.
So, I might add a condition, H2 > $10,000, and so here I have two different conditions.
In order for this to be paid, both F2 has to be > 20,000, H2 has to be > 10,000, and I can put multiple commas in there to have as many different conditions as I want.
So, let's copy this one down and we'll just do a quick test here.
Let's change the PROFIT to 9000 and, sure enough, that BONUS goes away.
We'll undo.
Okay.
So, we have the AND function.
[ =IF(AND(F2>20000,H2>10000),0.01*F2,0) ] Sometimes, we have a situation we're paying a bonus in the case of either one thing or another thing.
For example, maybe we always want to pay any sales to TEXACO.
There's an incentive and so, in that case, we're not just going to use the AND function.
We’ll change that to an OR function.
So, if F2 is > 20,000, or D2 is = to, in “, Texaco, close the AND function, then we pay a bonus, otherwise we pay 0, and copy that down.
So, here, we picked up a bonus to TEXACO even though their sale wasn't > $20,000.
[ =IF(OR(F2>20000,D2=“Texaco”),0.01*F2,0) ] Now, the other function that we have is NOT.
Sometimes you need to check to see if something is not true, and it's possible to nest multiple instances of AND, OR, and NOT to build any kind of strange condition you would ever need to build.
So, it gets, admittedly, a little bit more confusing when you have two conditions but it does allow you to build IF statements that will handle just about anything.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, now, yesterday, I said that we had someone who asked about using an IF function with two conditions and I really went down the wrong path here.
I explained how to have two different IF statements rolled in together.
That's probably really not what they were asking for.
What they probably wanted to do is set up an IF statement where we pay the bonus only if two different conditions are met.
So, yesterday, we said the bonus is paid anytime the revenue is > $20,000.
Maybe we also need a second condition that the profit has to be > $10,000.
Well, to do two conditions is pretty tough here in this logical test.
There's no way to really put an AND statement or, you know, to indicate the two things need to be joined by an AND.
So, we have to use another function.
It's called the AND function, put in (, and now, in commas, we can put as many conditions that we want inside the AND function.
So, I might add a condition, H2 > $10,000, and so here I have two different conditions.
In order for this to be paid, both F2 has to be > 20,000, H2 has to be > 10,000, and I can put multiple commas in there to have as many different conditions as I want.
So, let's copy this one down and we'll just do a quick test here.
Let's change the PROFIT to 9000 and, sure enough, that BONUS goes away.
We'll undo.
Okay.
So, we have the AND function.
[ =IF(AND(F2>20000,H2>10000),0.01*F2,0) ] Sometimes, we have a situation we're paying a bonus in the case of either one thing or another thing.
For example, maybe we always want to pay any sales to TEXACO.
There's an incentive and so, in that case, we're not just going to use the AND function.
We’ll change that to an OR function.
So, if F2 is > 20,000, or D2 is = to, in “, Texaco, close the AND function, then we pay a bonus, otherwise we pay 0, and copy that down.
So, here, we picked up a bonus to TEXACO even though their sale wasn't > $20,000.
[ =IF(OR(F2>20000,D2=“Texaco”),0.01*F2,0) ] Now, the other function that we have is NOT.
Sometimes you need to check to see if something is not true, and it's possible to nest multiple instances of AND, OR, and NOT to build any kind of strange condition you would ever need to build.
So, it gets, admittedly, a little bit more confusing when you have two conditions but it does allow you to build IF statements that will handle just about anything.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
