Functions AND and OR are 2 logic functions that help you to create sophisticate logical tests.
Presentation of the functions
These functions are very useful with logical tests.
Basically, with a logical test you compare 2 items only. But if you want to create a test with more items, you have to use these functions.
AND function
A condition AND returns TRUE if and only if all tests in parenthesis are true.
=AND(test 1,test 2,test 3, ...)
OR function
A condition OR returns TRUE if one (or more) tests of the function is true.
= OR(test 1,test 2,test 3, ...)
XOR function
This is a function available only for Excel 2013 and up but is used is very limited. This function is useful for electronic projects.
With the OR function, one or more test have to be TRUE to return TRUE. But with the XOR function only one tests of the function must be true. If you have more than one test TRUE, the function will return FALSE
Have a look at this article if you want to learn more about it.
Convert TRUE/FALSE to 0/1
Whatever your test, single test or with the functions AND or OR, the test will return TRUE or FALSE
But, you can change that by multiply your test by 1 to return 1 if it's TRUE or 0 when it's FALSE 😃😋
=8>1 => TRUE
And with *1 the result is
=(8>1)*1 => 1
Examples with AND or OR functions
Let's take this customer file. You want to extract some customers in function of criteria for your business.
Let's consider these different cases.
- How many clients are married with one child
- How many clients are single or divorced
- How many clients are married with an income above 75,000
- How many clients are male, unmarried and without children
- How many clients are women with 1 or 2 children.
- How many clients are married or in relationship, with an income greater than or equal to 50,000 and have at least two children.
- The answer for the question 1 is
=AND(E2="Married",F2=1)*1
- The answer for the question 2 is
=OR(E2="Single",E2="Divorced")*1
- The answer for the question 3 is
=OR(E2="Married",D2>75000)*1
- The answer for the question 4 is
=AND(C2="Man";E2="Single";F2=0)*1
- The answer for the question 5 is
=AND(C2="Woman";OR(F2=1;F2=2))*1
- The answer for the question 6 is
=AND(OR(E2="Married",E2="Relationship"),D2>=50000,F2>=2)
Include a test in a IF function
Once you have create your complex logical test with the function AND or OR, you can include them in the function IF to customize the result returned.
