Excel practice › Excel functions › AND, OR, NOT
AND, OR and NOT in Excel: combine conditions, with examples you can edit
AND returns TRUE when every condition you give it is true. OR returns TRUE when at least one is. NOT turns TRUE into FALSE and FALSE into TRUE. They are mostly used as the test of an IF: =IF(AND(B2="Meals", C2<=75), "Within limit", "Check").
Each condition is a complete comparison, and the conditions are separated by commas.
Syntax
=AND(logical1, [logical2], ...) · =OR(logical1, [logical2], ...) · =NOT(logical)
logical1- The first condition: a comparison that comes out TRUE or FALSE, like
C2<=75. logical2, ...- More conditions, up to 255. AND is TRUE only when all of them are true. OR is TRUE when any one of them is.
logical- NOT takes a single condition and returns the opposite.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Check |
| 2 | J. Kim | Meals | 64 | Yes | TRUE |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
75 to 50: one condition fails and the result is FALSE.Inside IF
On their own, the three functions return TRUE or FALSE. Put one where IF expects its condition and you choose what to show instead. This rule sends an expense to review when it is over 500 or has no receipt. A. Novak's 310 is under the limit, but the receipt is missing:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Check |
| 2 | J. Kim | Meals | 64 | Yes | |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | Review |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
OR to AND and the same row comes out OK, because both would have to be true.The mistake: AND where you mean OR
"Travel and Meals get a per diem" sounds like AND. But a formula looks at one row at a time, and one cell can't be Travel and Meals at once, so this AND is never true. J. Kim's expense is a meal and still comes out Standard:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Rule |
| 2 | J. Kim | Meals | 64 | Yes | Standard |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
AND to OR and the cell shows Per diem.The mirror image happens with ranges of numbers. "Between 100 and 500" needs both ends at once: AND(C2>=100,C2<=500). With OR, every number passes, since any number is either at least 100 or at most 500.
Write each condition out in full. OR(B2="Travel",B2="Meals") has two comparisons. In OR(B2="Travel","Meals") the second argument is a text on its own, not a condition.
NOT
NOT reverses one condition. It reads well in front of a longer test, like NOT(OR(…)) for "none of these". For a single comparison, the not-equal operator <> does the same job:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | No receipt |
| 2 | J. Kim | Meals | 64 | Yes | |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | TRUE |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
D4="Yes" is FALSE and NOT turns it into TRUE. =D4<>"Yes" gives the same result.Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
An expense is paid without review when the amount is 100 or less and there is a receipt. In E2, return TRUE or FALSE for J. Kim's expense.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Auto-pay |
| 2 | J. Kim | Meals | 64 | Yes | |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
Show the answer
=AND(C2<=100,D2="Yes") gives TRUE.
AND returns TRUE only when every condition is true: 64 is at most 100, and the receipt is Yes.
An expense needs review when the amount is over 500 or the receipt is missing. In E4, return the text Review or OK for A. Novak's expense.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Status |
| 2 | J. Kim | Meals | 64 | Yes | |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
Show the answer
=IF(OR(C4>500,D4="No"),"Review","OK") gives Review.
OR is true when at least one condition holds. Here the amount is fine and the receipt is missing, which is enough.
Meals and travel need a receipt once they go over 75. In E6, return Missing receipt when the category is Travel or Meals, the amount is over 75 and the receipt is No. Otherwise return OK.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Category | Amount | Receipt | Status |
| 2 | J. Kim | Meals | 64 | Yes | |
| 3 | R. Patel | Travel | 842 | Yes | |
| 4 | A. Novak | Software | 310 | No | |
| 5 | L. Brooks | Travel | 129 | Yes | |
| 6 | M. Diaz | Meals | 96 | No | |
| 7 | T. Hughes | Supplies | 515 | Yes |
Show the answer
=IF(AND(OR(B6="Travel",B6="Meals"),C6>75,D6="No"),"Missing receipt","OK") gives Missing receipt.
The OR answers one question, "is it travel or a meal?", and counts as a single condition inside the AND. The AND then needs all three: the right category, over 75, no receipt.
Questions people ask
How do I use IF with AND and OR together?
Put one inside the other, and the result in the IF's test: =IF(AND(OR(B2="Travel",B2="Meals"),C2>75),"Check","OK"). The inner OR is one condition of the AND. Read it from the inside out: first "travel or meals", then "and over 75".
What is the difference between AND and OR in Excel?
AND is TRUE only when all of its conditions are true. OR is TRUE when at least one is. With the conditions "over 500" and "no receipt", AND picks only the expenses that have both problems, and OR picks the ones that have either.
Can I use AND or OR without IF?
Yes. On their own they return TRUE or FALSE, which is enough for a helper column or a conditional formatting rule. Wrap them in IF when you want to show something else, like a text or a number.
How do I check if a number is between two values?
With AND and two comparisons: =AND(C2>=100,C2<=500). OR doesn't work for this: every number is either at least 100 or at most 500, so it would always be TRUE.