Condition

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")

Select an hotel in function of some criteria

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.

XOR returns TRUE only when only 1 condition = TRUE not 2 conditions

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

Convert logical test by multiply by 1

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.

Customer details

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

How many people get married with one child
  • 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.

IF function with a logical test with AND function

Related posts

Create test on String Length

Frédéric LE GUEN

Function COUNTIF

Frédéric LE GUEN

Function IF – Examples with payments

Frédéric LE GUEN

2 comments

Bryan Daniel E. Madrio 24/11/2020 at 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!

Reply
Frédéric LE GUEN 24/11/2020 at 11:13

Hi, yes, good catch. It's corrected. Thanks

Reply

Leave a Comment