↑ Return to Condition

AND – OR Functions

Functions AND and OR are 2 logic functions that are mostly used with the IF function.

Presentation

These functions are often attached to the IF function but can also be used with tools of data validation or conditional formatting updates.

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, …)

Excel 2010 allows up to 255 arguments in functions AND & OR 😉

 

Exercise

In the following file, you have various informations about your customers. You want to extract of this file a part of your customers on certain criteria.

If the criteria match your expectations, you will enter in column G 1 if you rest is True and 0 if test is not conclusive. Then, you make a SUM of the column G to see how many customers match your criteria.

Try to solve all these issues in the Excel workbook below.

  • 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.

If your logical test is based on a string you must write your string between double-quotes, if it’s a numeric, leave it like it is

The answer for the question 1 is:

=IF(AND(E2=”Married”,F2=1),1,0)

The answer for the question 2 is:

=IF(OR(E2="Single",E2="Divorced"),1,0)

The answer for the question 3 is:

=IF(OR(E2="Married",D2>75000),1,0)

The answer for the question 4 is:

=IF(AND(C2="Man";E2="Single";F2=0);1;0)

The answer for the question 5 is:

=IF(AND(C2="Woman";OR(F2=1;F2=2));1;0)

The answer for the question 6 is:

=IF(AND(D2>=50000;F2>=2;OR(E2="Married";E2="relationship"));1;0)




8 comments

Skip to comment form

  1. Anonymous

    where is the table i c’t find anyone

    1. Frédéric LE GUEN

      What is your question?

  2. ChrisB

    Hi. My students and I agree with both Cska and Inbal above.
    Q3 is =IF(AND(E2=”Married”,D2>75000),1,0)
    Q4 is =IF(AND(C2=”Man”,OR(E2=”Single”,E2=”Relationship”,E2=”Divorced”),F2=0),1,0)

    1. Francesca

      What about this Q4, a little shorter
      =IF(AND(E2″married”;F2=0;C2=”man”);1;0)

      1. Francesca

        sorry,
        =IF(AND(E2″married”;F2=0;C2=”man”);1;0)

        1. Francesca

          I don’t know why but in my formula, after E2, there are 2 signs minor and major as “different from”, and they are not shown in it

  3. Inbal

    In question 4, you were looking for the unmarried , but you only wrote “single” in the formula, what about the rest of the categories? divorced, relationship ect……..?

  4. cska

    Why don’t we use AND function in the case of question 3?

Leave a Reply

%d bloggers like this: