Logical Decisions, Multi-Branch Rules & Error Guards

Who this is for

Stage 2: Logical Decisions, Conditional Math & Dynamic Formulas — Designed for learners mastering if & blank guards, and / or / not / xor gates, ordered ifs & switch, ifna vs iferror & type validation through interactive spreadsheet examples.

What you will practise

• Write a blank-guarded IF + ISBLANK formula in D2:D5 that returns "" if On-hand (B) is blank, "Reorder" if B < Reorder point (C), and "OK" otherwise.
• Compute Rebate in C5:C8 using IF with locked $B$1 and $B$2 in Step 1, then compute Net Invoice (=B5-C5) in D5:D8 and Total Rebates (=SUM(C5:C8)) in C9 in Step 2.
• Write =IF(AND(B4>=$B$1, C4>=$B$2), "Auto-Approve", "Hold") across D4:D7.
• Write =IF(OR(B2>2, C2="Yes"), "Escalate", "Clear") across D2:D5.
• Compute boolean completeness in D2:D5 using AND, NOT, and ISBLANK in Step 1, then translate D2:D5 into "Ready" vs "Incomplete" in E2:E5 using IF in Step 2.
• Write =IF(XOR(B2>0, C2>0), "Valid", "Audit") across D2:D5.
• Assign Commission Rate in C2:C5 using descending IFS (>=100000: 0.11, >=50000: 0.07, fallback TRUE: 0.03) in Step 1, and compute Payout (=B2*C2) in D2:D5 in Step 2.
• Classify freezer probes B2:B5 into "Too Cold" (< -22), "In Spec" (-22 to -18 inclusive), or "Warm Breach" (> -18) in C2:C5 using IFS or clean nested IF.

Your learning path

1. Sum If Blank ("") vs. Not Blank ("<>") With SUMIF & SUMIFS

   Total open accounts-receivable invoices where Paid_Date is blank using SUMIF(D2:D6, "", C2:C6), settled invoices where Paid_Date is non-blank using "<>", and multi-criteria open region balances with SUMIFS.

2. Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)

   Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).

3. Average & Sum If Companion Column Is Not Blank (and Exclude Zero Placeholders) with AVERAGEIFS

   Average or sum values in column C only where companion column B is populated ("<>") while optionally excluding unrecorded 0 placeholders ("<>0") without triggering #DIV/0!.

4. 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.

5. SUMIFS with Multiple Criteria and OR Logic Without Double-Counting Rows

   Distinguish safe same-column OR addition in SUMIFS from overlapping cross-column OR conditions where naive SUMIF + SUMIF double-counts rows that satisfy both rules.

6. Lookup Across Selectable Rate Tables Without Volatile INDIRECT (IFS & VLOOKUP)

   Route SKU price lookups dynamically between Standard and Partner price books based on a customer tier cell using non-volatile IFS and VLOOKUP.

7. IFNA vs IFERROR: handle missing lookups without hiding broken data

   A missing lookup key produces #N/A, whereas a zero pack size or broken reference produces a different error. Use IFNA so genuinely missing codes show a friendly label while real arithmetic mistakes stay visible.

8. Sum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)

   Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.