Level 2 · Thank-you notes with AND and OR

An IF test can only be one condition, like C2>=500. Anna’s rule has three pieces: the channel is web, or the channel is app, and the order is at least $500. AND and OR combine several conditions into one TRUE or FALSE that IF can use.

AND and OR

Function	TRUE when	Example on row 4 (order 9003, web, 1014.26)
AND(a, b, …)	every condition is TRUE	AND(B4="web",C4>=500) is TRUE
OR(a, b, …)	at least one is TRUE	OR(B4="web",B4="app") is TRUE

Each takes as many conditions as you like, separated by commas. Neither shows a label on its own; it returns TRUE or FALSE, which is exactly what the first slot of IF expects.

Nesting them in the right order

Write the rule as a sentence first: “(web OR app) AND at least $500”. The brackets in the sentence become the nesting in the formula:

=IF(AND(OR(B2=$G$1,B2=$G$2),C2>=$G$3),"yes","no")

OR answers “is it online?”, and AND combines that answer with the amount. Row 2 is order 9001, a marketplace order of 954.98: the amount passes, the channel doesn’t, so “no”. Row 21 is order 9021, an app order of 47.97: the channel passes, the amount doesn’t, so “no” again. Only orders that pass both parts get “yes”.

The channel names and the amount sit in G1:G3, locked with $, for the same reason as the shipping threshold in chapter 1: when Anna moves the bar to $750, she changes one cell. Text comparisons ignore case, so B2=$G$1 matches “web”, “Web” and “WEB”.

NOT flips a test

NOT turns TRUE into FALSE and back. Anna’s rule can also be said as “not marketplace”:

=IF(AND(NOT(B2="marketplace"),C2>=$G$3),"yes","no")

On this sheet it gives the same 13 notes, because there are only three channels. The two versions differ the day a fourth channel appears: the OR version leaves it out until someone adds it, the NOT version lets it in. Pick the one that says what the rule really means. B2<>"marketplace" does the same as NOT(B2="marketplace") with less typing.

AND versus multiplying

You will meet this older trick in shared workbooks:

=IF((B2="web")*(C2>=500),"yes","no")

In arithmetic, TRUE counts as 1 and FALSE as 0, so multiplying two tests gives 1 only when both are TRUE, and IF treats any non-zero number as TRUE. It works, but it reads like maths, not like a rule. The + version of OR is worse: on row 4, OR(B4=$G$1,B4=$G$2)+(C4>=$G$3) is 2, not TRUE, and a SUM of such a column counts some orders twice. Use AND and OR, and keep the multiplying trick for formulas like SUMPRODUCT that need numbers.

Checking the result

Filled down to row 38, 13 orders get “yes”. A quick cross-check: 15 orders in the sheet are $500 or more, and 2 of those are marketplace orders (9001 and 9002), which leaves 13. Two numbers that agree from different directions are a good sign the formula says what the ticket says.

Common mistakes

• One big OR. OR(B2=$G$1,B2=$G$2,C2>=$G$3) is TRUE for any web or app order of any size and for every big marketplace order: 33 notes instead of 13.
• AND where you meant OR. AND(B2=$G$1,B2=$G$2) asks for a channel that is web and app at the same time. It is never TRUE, so nobody gets a note.
• > instead of >=. “$500 and up” includes $500.00 exactly.