Excel AND OR Logical Functions

Logic Functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or live online Excel classes.

AND Function

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.

OR Function

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.

Example 1

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.

Example 2

So let's say someone is looking to get that car loan, and they played lotto the night before. Their numbers match 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 to the dealership and again, they're disappointed to see that their loan approval status has not changed, because they need both $99 and the job.

Example 3

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 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 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 function.

Exercise 1

So let's take a look at an exercise.

Who is 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 equal, 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. The second person, Trevor, is 18, so they are the right age, and they passed the test. That's why their legal driver's 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.

Recap

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 statement when reviewing criteria.