Logical
Diagnose & Prevent #NUM!, #REF!, and #NAME? Formula Errors
Prevent #NUM! domain errors (negative SQRT/LOG or k > COUNT in LARGE/SMALL), #REF! out-of-bounds INDEX lookups, and #NAME? unquoted strings or function typos using proactive boundary guards.
Catch domain and bounds violations BEFORE #NUM! or #REF! cascades!
Blanket IFERROR wrappers hide broken formulas indiscriminately—turning a misspelled function name (#NAME?) or deleted column (#REF!) into a silent zero! Instead, inspect what each error code actually diagnoses: #NUM! means an impossible mathematical domain (such as SQRT(-16), LOG(0), IRR failing to converge, or requesting LARGE(A2:A5, 6) when only 4 numbers exist)!
#REF! signals an invalid cell reference—either a deleted sheet/column or an out-of-bounds INDEX(A2:A5, 9) row number exceeding ROWS(A2:A5)—while #NAME? means an unrecognized function identifier or an unquoted text literal (writing =IF(A2=Active, 1, 0) instead of "Active")! Testing domain bounds explicitly with IF(...) prevents #NUM! and #REF! cleanly while still surfacing genuine #NAME? syntax mistakes!
| Error Code | Typical Root Cause | Silent IFERROR Risk | Targeted Domain Guard |
|---|---|---|---|
| #NUM! | SQRT(x) with x < 0, or LARGE(rng, k) with k > COUNT(rng) | Masks invalid rank k or negative variance | IF(k > COUNT(rng), "Rank Exceeds Count", LARGE(rng, k)) |
| #REF! | INDEX(rng, r) where r < 1 or r > ROWS(rng) | Returns 0 as if row existed | IF(OR(r < 1, r > ROWS(rng)), "Row Out of Bounds", INDEX(rng, r)) |
| #NAME? | Misspelled function VLLOKUP or unquoted text Active | Hides broken formula syntax! | Fix spelling/quotes—do NOT mask #NAME? with IFERROR! |
Domain-Guarded K-th Largest Value (Prevents #NUM! When k > Count)
Validates 1 <= k <= COUNT(A2:A5) before calling LARGE(A2:A5, D2) so requesting top-5 from 4 numbers returns a clear diagnostic.
=IF(OR(D2 < 1, D2 > COUNT(A2:A5)), "Invalid Rank k (Exceeds Count)", LARGE(A2:A5, D2))Bounds-Guarded INDEX Row Lookup (Prevents #REF! Out-of-Range)
Checks 1 <= D3 <= ROWS(A2:A5) before evaluating INDEX(A2:A5, D3).
=IF(OR(D3 < 1, D3 > ROWS(A2:A5)), "Row Index Out of Bounds", INDEX(A2:A5, D3))Try the example
Use the original values or change them to test your understanding.