Logical & Validation
IF NOT This or That: Avoid the Always-True OR(<>X, <>Y) Trap
Apply surcharges or workflow rules whenever a cell equals neither X nor Y using NOT(OR(cell=X, cell=Y)) or AND(cell<>X, cell<>Y).
Why OR(B2 <> "Pickup", B2 <> "Local") is ALWAYS TRUE
In plain English we say: "Add an 8% fuel fee if shipping mode is not Pickup or Local." Translating that word-for-word as OR(B2 <> "Pickup", B2 <> "Local") creates a notorious bug: when B2 is "Pickup", it is not "Local" (TRUE), and when B2 is "Local", it is not "Pickup" (TRUE) — so every single row gets charged!
By De Morgan's Law, "neither X nor Y" is NOT(OR(B2 = X, B2 = Y)), which is logically identical to AND(B2 <> X, B2 <> Y).
| Order | Delivery Mode | Order Subtotal | Exempt (Pickup/Local)? | Fuel Fee (8%) |
|---|---|---|---|---|
| SHP-10 | Air Freight | $500.00 | No | $40.00 |
| SHP-11 | Pickup | $300.00 | Yes (Exempt) | $0.00 |
| SHP-12 | Local | $250.00 | Yes (Exempt) | $0.00 |
| SHP-13 | Ground Parcel | $400.00 | No | $32.00 |
Row-Level Surcharge When Mode Is Neither Exempt Option (Row 2)
Applies surcharge rate B8 (8%) to C2 only when B2 is non-blank and matches neither B6 ("Pickup") nor B7 ("Local").
=IF(ISBLANK(B2), 0, IF(NOT(OR(B2 = $B$6, B2 = $B$7)), ROUND(C2 * $B$8, 2), 0))Total Surcharge Across Non-Exempt Orders
Sums the 8% fuel fee across all rows in B2:C5 whose mode equals neither B6 nor B7.
=ROUND(SUMIFS(C2:C5, B2:B5, "<>", B2:B5, "<>" & B6, B2:B5, "<>" & B7) * B8, 2)Try the example
Use the original values or change them to test your understanding.