AND and OR practice exercises

Combine conditions with AND (all must be true) and OR (one is enough), usually inside IF.

Start the first exercise

How AND and OR work in Excel

AND and OR test several conditions at once. AND returns TRUE only when every condition is true. OR returns TRUE when at least one is. On their own they give TRUE or FALSE, so they usually sit inside the test of an IF.

=IF(AND(B2>=5,C2>=4.5),"Yes","No") gives Yes only when B2 shows at least 5 deals and C2 a rating of 4.5 or more. =IF(OR(B2="Gold",C2>=500),"Yes","No") gives Yes for Gold customers or for orders of 500 or more. You can nest one inside the other: =IF(AND(B2="DK",OR(C2>=500,D2="Yes")),"Free","Paid") gives Free only for orders in DK, and then only when the order is 500 or more or the customer is a member.

A common mistake is writing a range check the way you would on paper. C2<=B2<=D2 does not work in Excel. Test each side on its own inside AND instead, as in =AND(B2>=C2,B2<=D2). The exercises below cover a bonus with AND, free shipping with OR, AND and OR in one test, and checking that a value is within limits.

  1. 1Two conditions with ANDA seller gets a bonus only with at least 5 deals AND a rating of at least 4.5. In D2, use IF with AND to show "Yes" or "No".
  2. 2Either condition with ORPremiumShipping is free for Gold customers OR for orders of 500 or more. In D2, use IF with OR to show "Yes" or "No".
  3. 3AND and OR in one testPremiumDelivery is free only inside DK, and then only for orders of 500 or more OR for members. In E2, use IF with AND, and OR inside the AND, to show "Free" or "Paid".
  4. 4Check a value is within limitsPremiumIn E2, show "OK" if the measurement in B2 is between the limits in C2 and D2, both included, and "Check" if not. Both tests must be true, so use AND inside IF.

Functions in this topic

Read the guide

Questions

Can I use AND or OR without IF?

Yes. Both return TRUE or FALSE on their own, so =AND(B2>=5,C2>=4.5) in a cell shows TRUE or FALSE. That works well as a check column next to your data, or as the formula in a conditional formatting rule.

How do I test if a cell is one of several values?

Avoid a long OR. Put the allowed values in a range, such as E2:E5, and use =COUNTIF(E2:E5,A2)>0. It returns TRUE when A2 appears in the list. For just two or three values, =OR(A2="Red",A2="Blue") is fine.

How many conditions can AND and OR take?

Up to 255 in Excel, separated by commas. With more than a handful, the formula gets hard to read. A lookup table or IFS is usually clearer, and a table is much easier to change later when a rule changes.