Functions AND – OR

Reading Time: 3 minutes

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 than 1 condition, you must use this function. Examples at the bottom of this article.

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 hotel with

• At least 3 stars (B2>=3)
• The price of the menu is maximum 35 (C2<=35)
• And the hotel has a Fitness room (E2="Yes")

So the formula is

=AND(B2>=3,C2<=35,E2="Yes")

As you can see, if test returns TRUE only if all the arguments of the function are TRUE.

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

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) 😉👍

Few situations solve with AND or OR functions

Let's take this customer file. You want to extract some customers in function of criteria for your business.

Solve these questions

Let's consider these different cases.

1. How many clients are married with one child
2. How many clients are single or divorced
3. How many clients are married with an income above 75,000
4. How many clients are male, unmarried and without children
5. How many clients are women with 1 or 2 children.
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.

Answer

• 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

=AND(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.