Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 93 ยท Logical and Conditional Formulas

AND, OR, NOT and XOR

Combine several conditions in Excel with AND, OR, NOT and XOR, use TRUE and FALSE as numbers, and avoid the errors AND and OR can cause.

A single comparison such as B2>=50 answers one question. Real decisions usually need several: "Is the customer a member and is the total at least 100?", "Is the day a Saturday or a holiday?". Excel's logical functions AND, OR, NOT and XOR combine conditions into one TRUE or FALSE, and you normally feed that result to IF.

This lesson also explains something that makes advanced formulas possible: in Excel, TRUE and FALSE behave like the numbers 1 and 0 when you do arithmetic with them. As in the previous lesson, the formulas are real Excel formulas, and the live examples run on a small JavaScript stand-in engine, not on Excel itself.

Four ways to combine tests

AND TRUE only if every test is TRUE.
OR TRUE if at least one test is TRUE.
NOT Flips TRUE to FALSE and back.
XOR TRUE if an odd number are TRUE.

Each returns TRUE or FALSE, ready for IF.

1 / 2

AND, OR and NOT

TEXT
=AND(logical1, [logical2], ...)
=OR(logical1, [logical2], ...)
=NOT(logical)

AND returns TRUE only when all of its arguments are true. OR returns TRUE when at least one is true. NOT takes a single value and reverses it. You can pass up to 255 arguments to AND and OR, and each argument can be a comparison, a cell holding TRUE/FALSE, or a range of such cells.

TEXT
=IF(AND(B2="Member", C2>=100), C2*0.9, C2)
=IF(OR(WEEKDAY(A2,2)>5, COUNTIF(Holidays,A2)>0), "Closed", "Open")
=IF(NOT(ISBLANK(D2)), "Filled", "Empty")

The first formula gives members a 10% discount on orders of 100 or more. The second marks weekends and listed holidays as closed. The third is easier to read as D2<>"" for most people; NOT shines when you wrap a function that returns TRUE/FALSE, such as ISBLANK.

XOR: exactly one

XOR is the "exclusive or". With two arguments it is true when exactly one of them is true. With more arguments it is true when an odd number of them are true. It is rarely needed, but it is perfect for "one or the other, not both" rules, such as a form where either a phone or an email must be given but not both.

JS Truth tables for the logical functions

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Using them inside IF

Because each function returns one logical value, the combination slots straight into the test of IF. Here is a small shipping rule: orders ship free if they are at least 50 and the destination is domestic, or if the customer is a VIP.

JS Free shipping with AND and OR in IF

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice the nesting: AND sits inside OR. Read it from the inside out: "(total at least 50 and domestic) or VIP". Use parentheses and, in long formulas, line breaks (Alt+Enter) to keep such rules readable.

TRUE and FALSE are numbers in disguise

A comparison displays TRUE or FALSE, but when you do arithmetic on it, Excel converts TRUE to 1 and FALSE to 0:

TEXT
=TRUE+TRUE        โ†’ 2
=(B2>5)*10        โ†’ 10 if B2 is above 5, otherwise 0

This gives a second way to combine conditions. Multiplying two tests acts like AND (1ร—1 is the only way to get 1), and adding them acts like OR (the sum is above 0 when either is true). The pattern (test1)*(test2) is the core of array-style formulas such as SUMPRODUCT.

The double unary

A comparison result is a logical value, not a number. Some functions (and the = check against a number) treat logical values differently from numbers. To convert explicitly, put a minus sign twice in front: --(B2>5). The first minus turns TRUE into -1 and the second makes it 1 again. You will see -- often in older formulas; N() or multiplying by 1 do the same job.

JS Logical values as numbers, and the double unary

Press Run (or Ctrl + Enter) to see the console output here.

No output.

AND and OR evaluate every argument

Unlike some programming languages, Excel's AND and OR do not stop at the first false or true argument. They always evaluate all arguments, so an error in a later argument spoils the result even when an earlier argument already decided it. A classic mistake is guarding a division with AND:

TEXT
=IF(AND(A1<>0, 10/A1>2), "yes", "no")      โ† #DIV/0! when A1 is 0
=IF(A1=0, "no", IF(10/A1>2, "yes", "no"))   โ† safe: IF evaluates only the chosen branch
JS Why AND is not a safe guard

Press Run (or Ctrl + Enter) to see the console output here.

No output.

When A1 is 0 the AND version fails with #DIV/0! because 10/A1 is still calculated. The nested IF never reaches the division. See Formula Errors for how errors travel through formulas.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which formula returns TRUE when A1 is 5 and B1 is 200?

    Both tests of the third formula are true (5 is below 10, 200 is above 100). The first formula has two false tests, the second has a false first test, and NOT(A1<10) is FALSE.
  2. 2. What does =XOR(TRUE, TRUE, TRUE) return?

    With more than two arguments XOR is TRUE when an odd number of them are TRUE. Three is odd.
  3. 3. What is the value of =(5>3)*(2>9)?

    The first test is TRUE (1), the second FALSE (0), and 1 ร— 0 is 0. Multiplying tests acts like AND.
  4. 4. Why can =IF(AND(A1<>0, 10/A1>2), "yes", "no") return #DIV/0!?

    AND and OR do not stop early. Use a nested IF to guard the risky part.

Summary

  • AND needs all tests true, OR needs at least one, NOT reverses a value, XOR is true for an odd number of true values.
  • They return TRUE or FALSE, so they usually serve as the test inside IF; nest them with parentheses for rules like "(A and B) or C".
  • In arithmetic TRUE is 1 and FALSE is 0, so (test1)*(test2) acts as AND and (test1)+(test2) as OR.
  • --(test) converts a logical value into a plain 1 or 0.
  • AND and OR evaluate every argument; guard errors with a nested IF, not with AND.

Next: IFS, SWITCH and Choosing Between Many Cases.