Learn to use an IF statement with either an AND function or an OR function to expand the functionality of the IF function by adding an AND or an OR criteria into the logical test.
IF statements (beginner and nested functions) are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.
IF Statement with and or
You'll use an IF statement with either an AND function or an OR function to expand the functionality of the IF function by adding an AND or an OR criteria into the logical test. I'm going to do a quick review of the AND OR statement before we take a look at how you can combine that with the IF statement.
AND or Statement
So in this situation, I'm going to pretend there's a dealership that is going to give loan approvals to people who have at least $99 and a job. If we write that as an AND statement, it will look like this. Equal, and, this person needs to have at least $99, so that amount needs to be greater than or equal to 99. When I enter the comma, I could actually use the word "and, " and their employment status needs to be employed, so they need to have a job. J9 needs to be equal to "Job." That's how we're determining job status. Close parenthesis, press ENTER. That person is approved for the loan.
To save time writing the OR statement, I'll simply autofill down and go into the function and change the AND statement to an OR statement. Now I don't have to write as much. I'll press ENTER. And there you go.
Now the way this works for an AND statement, if someone doesn't have at least $99, that loan approval is false. Let's say they have $99, but the problem is they don't have a job. They are also not approved.
Now, when you get to the OR statement, only one of the criteria needs to be true. If someone has $50, they still are approved for the loan. And if their problem is that they don't have a job, it's actually not a problem. They still get the loan approval. The only way the OR statement will result in a false is if all the logical tests are false.
IF Statement with and or
All right, so now let's see how we can use an IF statement with this. All right, so I'm going to do this in a simple way. I want to be able to say someone is approved if they've met the criteria. I can't do that with the AND OR statement because as you can see, I only have true or false as the result of the AND OR statement.
So I'm going to type equal, IF, and I'm going to check their loan approval status, if it is true, then I'm going to say that that person was approved, and if they were not, I'm going to say not approved. I'll keep it really simple. Then I'll press ENTER, and there we go. I'll autofill this down. I don't have to change anything because I'm referencing the right value.
So how does this work if I change this to $50? The loan approval status is triggering the right response. I do want false to be equal to "Not Approved, " and I do want true to be equal to "Approved."
So I'm using this individual cell as a crutch, because what's really doing the work is the AND OR statement. In order to combine the IF statement with the AND OR statement, what I should do is go to this cell and replace K9 with the engine that's making the AND OR statement work and gives me a true or false in this cell.
So I'll go back to the IF statement and replace K9 with an AND OR statement. That's how you would use the AND OR statement with an IF statement to get either result. Now, I don't need to depend on K9. I could actually remove that value. But this may be a little daunting if you're writing a formula like this. This is why I showed you separately how to write the AND OR statement and then use it within the IF statement.
I'll press ENTER and the same results. If I go here and change this to 50, it goes to false and "Not Approved." If I go over and change it to 99, it updates.
Exercise
So let's take a look at an example in this exercise here.
We're looking to check the legal status of these three people. We want to see if they're legal drivers. Now in a prior exercise, true would mean that they were legal and false would mean they're not legal. Now we're able to actually use the words "legal" or "not legal.".
Now to start this out, maybe we'll just start out by writing the AND OR statement. In this case, this is an AND statement that we're working with, because you're legal if you're 17 or older and passed the road test. So this is going to be equal, and this value needs to be greater than or equal to 17. And that value needs to be equal to "Yes." Close parenthesis, and I'll press ENTER. True. So I get true all around for this first person. I'll autofill down, and I get true, true, false.
Now I want to change trues and falses to legal and not legal. Now I'll type an IF statement at the very beginning right after the equal sign. Open parenthesis, and I'm basically saying the logical test is the AND statement. Now, if that whole AND statement results in true, then I'll enter a comma and write what I want to display in the cell. In this case, "Legal." Then I'll enter a comma, and then write what I want to display if it's false, and that is "Not Legal." Close parenthesis, and I'll press ENTER.
So true became legal, and now we have true and false. If I autofill this down, we'll get the appropriate status for these drivers.
Recap
So that's how you would use the AND OR statement with an IF statement. Again, exercises like this can be complicated. If you click on the plus sign in the appropriate row, you'll be able to see our answer in the spreadsheet, and you can verify whether or not what you did matches our answer. We have that for the rest of the exercises in this sheet, including the ones I haven't done. So that is the IF statement with the AND OR.
