Enhance your Excel skills by combining IF statements with AND or OR functions to create more sophisticated logical tests. Learn practical examples such as loan approvals and driver legality checks to effectively apply these combined functions.
Key Insights
- Combine IF statements with AND or OR functions in Excel to expand logical testing capabilities, such as approving loans based on having at least $99 and employment status.
- Use OR statements when only one criterion needs to be met; for instance, granting loan approval either to customers who have at least $99 or are employed.
- Create clear and meaningful outputs by nesting AND or OR functions within IF statements, demonstrated through practical scenarios like checking driving legality based on age (17 or older) and passing a road test.
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.
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'll 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 can actually use the word and and their employment status needs to be employed so they need to have a job.
So J9 needs to be equal to JOB. That's how we're determining job status. Close parentheses, press ENTER, that person is approved for the loan.
To save time writing the or statement I'll simply auto fill 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 we go. Now the way this works for an and statement if someone doesn't have at least $99 their 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 need 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.
All right so now let's see how we can use an if statement with this. So in order to use an if statement, 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 auto fill 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 fifty dollars? 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.
So 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 give 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 you the 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 so this is why I showed you separately how to write the and or 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. 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. I mean 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 or statement. In this case it's an and statement that we're working with because you're legal if you're 17 or older and pass the road test.
So this is going to be equal to and this value needs to be greater than or equal to 17 and that value needs to be equal to yes. Close parentheses and I'll press ENTER. True.
So I get true all around for this first person. I'll auto fill 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 parentheses 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 what I want to display if it's false and that is not legal. Close parentheses and I'll press ENTER. So true became legal and now we have true and false.
If I auto fill this down we'll get the appropriate status for these drivers. So that's how you would use the and or or statement with an if statement. Again exercises like this can be complicated so 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 and so 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.