Data Validation & Hygiene
Classify and Count Formula Error Codes (#N/A vs. #DIV/0! and #VALUE!) for Workbook QA
Separate benign missing-lookup #N/A flags from severe arithmetic or data-type faults (#DIV/0!, #VALUE!) using ISNA and ISERROR priority triage.
Why blanket IFERROR hides two very different failure modes
Wrapping every calculation in IFERROR(formula, 0) silently masks both unmapped master-data keys (#N/A, Excel ERROR.TYPE = 7) and broken arithmetic or text corruption (#DIV/0! = 2, #VALUE! = 3). During financial close, unmapped SKUs belong on a master-data onboarding queue, whereas division-by-zero or corrupted text requires formula repair.
Testing ISNA(cell) first and ISERROR(cell) second classifies every row into "Clean", "Missing Key (#N/A)", or "Math/Type Fault" so workbook QA counters can track each error class independently.
| Row Calc | Evaluated Result | ISNA? | ISERROR? | QA Triage Class |
|---|---|---|---|---|
| Row 2: 240 / 4 | 60 | FALSE | FALSE | Clean |
| Row 3: XLOOKUP("SKU-99", ...) | #N/A | TRUE | TRUE | Missing Key (#N/A) |
| Row 4: 180 / 0 | #DIV/0! | FALSE | TRUE | Math/Type Fault |
| Row 5: "abc" + 10 | #VALUE! | FALSE | TRUE | Math/Type Fault |
| Row 6: 150 / 3 | 50 | FALSE | FALSE | Clean |
Row-Level Error Classification Formula (ERROR.TYPE Triage)
Checks ISNA(B3) before ISERROR(B3) so missing lookup keys are separated from calculation or type errors.
=IF(ISNA(B3), "Missing Key (#N/A)", IF(ISERROR(B3), "Math/Type Fault", "Clean"))Total Workbook Error Count & Severe Fault Count
Counts severe non-#N/A math/type faults in C2:C6 separately from missing lookup keys.
=COUNTIF(C2:C6, "Math/Type Fault")Try the example
Use the original values or change them to test your understanding.