Logical & Conditional Formulas
Compound Logic: IF Cell Is (X or Y) and Z (Nested AND + OR Rebate Gate)
Evaluate compound business eligibility rules—such as Tier is ("Gold" OR "Platinum") AND Status is "Active" AND Spend >= 1,000—without operator-precedence bugs.
Nesting OR inside AND prevents English phrasing ambiguity
In plain English, "grant an 8% rebate if Tier is Gold or Platinum and Account is Active" means ((Tier = "Gold") OR (Tier = "Platinum")) AND (Status = "Active"). When translated carelessly in languages or array expressions without grouping, AND binds tighter than OR—accidentally paying rebates to every "Gold" account even if Inactive!
Prefix function notation in spreadsheets eliminates precedence ambiguity explicitly: AND(OR(B2 = "Gold", B2 = "Platinum"), C2 = "Active", D2 >= $G$2) requires every outer AND clause to pass while allowing any valid tier inside OR.
| Tier | Status | Order Spend | OR(Gold, Platinum) | Full AND(...) Rebate Result |
|---|---|---|---|---|
| Gold | Active | 1,500 | TRUE | Eligible (120) |
| Gold | Suspended | 2,500 | TRUE | Ineligible (0 — fails Status) |
| Silver | Active | 3,000 | FALSE | Ineligible (0 — fails Tier) |
Compound AND + OR Rebate Formula
Place OR(...) as the first requirement inside AND(...), followed by status and minimum-spend gates.
=IF(AND(OR(B2 = "Gold", B2 = "Platinum"), C2 = "Active", D2 >= $G$2), ROUND(D2 * $G$3, 2), 0)Try the example
Use the original values or change them to test your understanding.