Functions AND and OR are 2 logic functions that help you to create sophisticated logical tests with Excel.
- The AND function
A condition AND returns TRUE if and only if all tests in parenthesis are true.
- The OR function
A condition OR returns TRUE if one (or more) tests of the function is true.
Presentation of the functions
The AND and OR functions are very useful with logical tests in Excel. Basically, a logical test compares two items only. But if you want to create a test with more than one condition, you must use this function.
AND function
A condition AND returns TRUE if and only if all tests in parenthesis are true.
=AND(test 1,test 2,test 3, ...)
For instance, you may have a list of hotels based on criteria such as the number of stars (classification), the price of the menu, etc. You want to select a hotel according to these criteria
- At least 3 stars (B2>=3)
- The price of the menu is a maximum of 35 (C2<=35)
- And the hotel has a Fitness room (E2="Yes")
So the formula is
=AND(B2>=3,C2<=35,E2="Yes")
And when you copy the formula for all the hotels, you have this result
As you can see, the test returns TRUE only if all the 3 criteria are TRUE.
OR function
A condition OR returns TRUE if one (or more) tests of the function is true. So, you use the OR function in a different situation where you accept many cases.
= OR(test 1,test 2,test 3, ...)
For instance, here, you want to select only the people who are single or divorced
=OR(F2="Single",F2="Divorced")
Combination of AND and OR functions
But also, you can combine the 2 functions to create more sophisticated tests. For instance, we still want to select people who are divorced or single but also with no child. The formula is
=AND(G2=0,OR(F2="Single",F2="Divorced"))
Explanations:
- First, the test will check the status Single or Divorced. The OR will return TRUE when one of these values is found.
- Then, we embed the previous in the AND function to return TRUE when both results are TRUE
Bryan Daniel E. Madrio
24/11/2020 @ 10:41
I'm asking for consideration to check the answer key for question 3 because I believe that the correct function for it would be "AND". Please reply to my comment if I'm right or wrong to also correct myself. Thank you!
Frédéric LE GUEN
24/11/2020 @ 11:13
Hi, yes, good catch. It's corrected. Thanks