Formula Auditing & Logic
Error-Proof a Complex Formula Chain: Targeted Stage Gates vs. Blanket IFERROR
Harden multi-step lookup, multiplication, and division pipelines by isolating missing lookup keys (IFNA), non-numeric text inputs (ISNUMBER), and zero denominators separately instead of masking bugs with IFERROR(..., 0).
Why wrapping an entire formula in IFERROR(..., 0) corrupts totals
Wrapping a multi-step calculation such as IFERROR((Units * XLOOKUP(SKU, Codes, Rates)) / Hours, 0) collapses four completely different failure modes—an unmapped SKU (#N/A), a text placeholder like "TBD" (#VALUE!), zero logged hours (#DIV/0!), and a misspelled function name (#NAME?)—into a silent 0 that understates averages and hides broken master data.
Using LET to inspect each stage explicitly returns actionable diagnostic codes ("UNMAPPED_SKU", "INVALID_QTY", "ZERO_HOURS") in the audit column while feeding clean validated numbers into summary totals.
| Pipeline Stage | Failure Mode | Targeted Guard | Diagnostic Output |
|---|---|---|---|
| 1. Rate Lookup | SKU missing from price table | IFNA(XLOOKUP(...), "UNMAPPED_SKU") | "UNMAPPED_SKU" |
| 2. Quantity Type | Text note ("TBD", "N/A") | NOT(ISNUMBER(B2)) | "INVALID_QTY" |
| 3. Division Base | Hours <= 0 | C2 <= 0 | "ZERO_HOURS" |
| 4. Clean Compute | All gates passed | ROUND((B2 * rate) / C2, 2) | Numeric rate/hr |
Multi-Stage Diagnostic Chain with LET
Bind the lookup result once, test ISTEXT for missing keys, verify numeric inputs, and guard the divisor before dividing.
=LET(unit_rate, IFNA(XLOOKUP(A2, $G$2:$G$4, $H$2:$H$4), "UNMAPPED_SKU"), IF(ISTEXT(unit_rate), unit_rate, IF(NOT(ISNUMBER(B2)), "INVALID_QTY", IF(C2 <= 0, "ZERO_HOURS", ROUND((B2 * unit_rate) / C2, 2)))))Safe Numeric Line Revenue Rollup
Keep numeric rollups error-free without polluting averages by gating line revenue on numeric rate and quantity.
=LET(unit_rate, IFNA(XLOOKUP(A2, $G$2:$G$4, $H$2:$H$4), -1), IF(AND(unit_rate >= 0, ISNUMBER(B2)), B2 * unit_rate, 0))Try the example
Use the original values or change them to test your understanding.