Functions AND and OR are 2 logic functions that help you to create sophisticated logical tests with Excel.
Presentation of the functions
The AND and OR functions are very useful with logical tests in Excel.
Basically, with a logical test, you compare two items only. But if you want to create a test with more than 1 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 have a list of hotels with different criteria like the number of stars (classification), the price of the menu, ...
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 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 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 will be found
- Then, we embed the previous in the AND function in order to return TRUE when both result are TRUE
Exercises online with AND and OR in Excel
To learn how to use these functions, please go to that link to do an online exercise
You will learn how to select your client according to criteria like Gender, Income, Age, .... And also how to combine many criteria to create a rich selection.
If you want to do more online exercises, go to that page.
XOR function
You can create an exclusive OR (only one test is true) with the XOR function. This function is generally useful for electronic projects.
Have a look at this article if you want to learn more about it.
Convert TRUE or FALSE to 0 or 1
Do you know that you can convert the result of a test (TRUE or FALSE) to a numeric value (1 or 0) 😉👍
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