Master the essentials of Excel's AND and OR logical functions to efficiently evaluate multiple criteria in your data analysis. Gain practical insight into real-world scenarios, like car loan approvals and driving eligibility, using these valuable Excel tools.
Key Insights
- The AND function returns TRUE only if all specified logical tests are true, illustrated by the requirement of having both at least $99 and employment status to qualify for a car loan.
- Unlike AND, the OR function returns TRUE if at least one logical test is satisfied; this is shown when loan approval is granted if a person either has $99 or employment, but not necessarily both.
- Practical examples, such as determining driving eligibility in New York state based on age (17 or older) and passing a road test, effectively demonstrate how the AND function evaluates multiple criteria within Excel.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
AND OR logical functions. In this section, we'll take a look at how you can use the AND OR functions to test if multiple criteria are all true or at least one of them is true. So the AND function checks to see if all logical tests are true.
If they are, the result of the AND function will be true. If only one or several logical tests are false, then the whole AND function returns false. Now the OR function is a little different.
There's a little bit of flexibility involved here. Only one logical test has to be true for the OR function to result in true. The only way you'll get a false with the OR function is if every single logical test is false.
So let's take a look at an example that relates to real life. I used to hear this ad on the radio or on TV where the salesman would come on in a real salesman-like way and say something along the lines of, if you have $99 and a job, you can get that car loan because all you need is $99 and a job. That is an AND statement.
If I were to write that as an AND statement, it would look like this. Equal AND. The first criteria is that the person must have at least $99.
That's going to be written as greater than or equal to 99. Now the second criteria, and this is where you can say the word AND when you add the comma, AND this value over here needs to be equal to job. Now remember you're working with text, so you need double quotes.
That's the two criteria you need to check to see if both logical tests are true. When I press ENTER, I get true because they're both true. Now I'll also take care of the OR statement by simply auto-filling down.
And I know some of you are going to say, hey, wait a minute. Isn't it still going to say AND? Yes, I know. I'll just go in and change the AND to OR.
At least I won't have to write all the other stuff that comes after it. I'll press ENTER and let's test this out. So let's say someone is looking to get that car loan and they played lotto the night before.
Their numbers matched the winning numbers and now they have a total amount of $10,000. Now the thing you need to know about this person is unfortunately, they hate their job. And with this new influx of money, they're going to gather up enough courage to quit that job they don't like.
And now they will have no job. When they go to the car dealership in the morning to get that car loan, they're going to be disappointed because they're going to get rejected because they do not meet the employment requirement. They're told you need to have a job in order for you to get this loan.
Now let's say this person spends a lot of their money and they're down to let's say $50. The only part of the conversation they heard is they needed to get a job in order to get the car loan. So they go back to the and again, they're disappointed to see that their loan approval status has not changed because they need both $99 and a job.
Now if we move over to the or function, if they have $50 and a job, nothing happens to their loan approval. It stays true. So with the or function, only one thing needs to be true.
So they met one criteria, which is the Now let's say they have $99, but they have no job. Unlike the prior situation where they had $10,000 and no job, here the loan approval is still true because one criteria has been met, the financial one. But if we go and change $99 to $50, that's when we'll get false.
So those are the thresholds for true or false when it comes to the and or or function. So let's take a look at an exercise. Who was allowed to drive in New York? There are two criteria that need to be true in order for someone to be able to drive in New York.
The person must be 17 or older and pass the road test. So this is an and statement. So I will type and this value must be greater than or equal to 17 and that value should be equal to yes.
I'll press ENTER and I will get the result of the legal driver status. The first person is underage. That's why they are not a legal driver, even though they passed the test.
Now the second person Trevor is 18, so they're the right age and they passed the test. That's why their legal driver status is true. And Shannon, while being the right age, did not pass the test and that's what keeps her from being a legal driver.
So that is the and or statement. We have other exercises that you can work with. On this particular sheet, you can see that there are grouping buttons and these grouping buttons allow you to look at the answer if you're struggling with it.
So you can do these exercises on your own and this is the basic process of working with the and or or statement when reviewing criteria.