Skip to main content
Garfield Stinvil/5 min read

AND & OR Functions: Logic in Real-Life Scenarios

Practical AND/OR Examples

Approval Workflows

AND(amount<5000, approver_signed) — both must be true.

Eligibility Checks

OR(years_exp>=3, has_certification) — either qualifies.

Conditional Formatting

Highlight rows where amount > target AND quarter = 'Q4'.

Data Validation

Restrict entries with logic like 'must be in stock OR special order'.

Master Excel at Noble Desktop

Noble Desktop's Excel Bootcamp covers formulas, pivot tables, data analysis, and VBA.

This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.

AND/OR logical functions. In this section, we'll take a look at how you can use the AND and OR functions to test if multiple criteria are all true or at least one of them is true.

AND/OR logical functions. In this section, we'll take a look at how you can use the AND 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 typical 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 the lottery 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 to get the car loan. So they go back again and 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 criterion, which is the job. 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 criterion 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 and OR functions. 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 and OR functions when reviewing criteria.