Functions AND and OR are 2 logic functions are used to create logical tests alone or with the IF function.
Sommaire
Presentation of the functions
These functions are use to enhance a logic test. 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 one of these functions.
AND function
A condition AND returns TRUE if and only if all tests in parenthesis are true.
OR function
A condition OR returns TRUE if one (or more) tests of the function is true.
XOR function
This is a function available only for Excel 2013 and up but is used is very limited (so don't really consider it)
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 post 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
=(8>1)*1 => 1
Example to use 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.
- Question 1: How many clients are married with one child
- Question 2: How many clients are single or divorced
- Question 3: How many clients are married with an income above 75,000
- Question 4: How many clients are male, unmarried and without children
- Question 5: How many clients are women with 1 or 2 children.
- Question 6: 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
These tests are very useful but return TRUE or FALSE it not the best way to present a result. The only way to customize a logic test is to insert it in a IF function
Related articles
Have a look at these other articles that could help you in your work
