Two conditions: AND and OR

A bonus is often given not for one achievement but for two at once. Say the rule is: sales of at least $100,000 and at least five new clients. For now, the formula in D2 checks only the sales, and one manager gets a bonus they shouldn't:

Two conditions in one check

One comparison checks one condition. To check two at once, you pass them to the AND or OR function, separated by a comma, like the arguments of any function:

=AND(B2>=100000,C2>=5)

Here B2 holds the sales, and C2 the number of new clients from the same row.

• AND answers TRUE only if all the conditions are true.
• OR answers TRUE if at least one of them is true.

The difference is easy to see on a single number with two boundaries. A medicine is stored at a temperature from 2 to 8 °C. The temperature is in range if it's no lower than 2 and at the same time no higher than 8. An alert goes off if it's lower than 2 or higher than 8. In the diagram, each condition shades the band where it's true. Move the temperature around, then switch to OR:

This is how Excel checks any "between": a temperature from 2 to 8 is written as =AND(B2>=2,B2<=8).

AND inside IF

AND and OR answer TRUE or FALSE, just like a plain comparison. That's why they go into IF in place of the condition:

=IF(AND(B2>=100000,C2>=5),"Bonus","No")

This formula has two pairs of parentheses, one inside the other. In the diagram, matching parentheses have the same color:

OR goes in the same place. For the medicine, the alert formula is:

=IF(OR(B2<2,B2>8),"Alert","OK")

All that's left is to fix the bonus table. Right now D2 checks only the sales, so add the condition about clients there:

AND and OR can hold more than two conditions. If a third one is added to the rules, for example about customer reviews, it goes inside the parentheses as one more argument after a comma.