Arte SheetsLearn. Try. Understand.

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 CodeTypical Root CauseSilent IFERROR RiskTargeted Domain Guard
#NUMSQRT(x) with x < 0, or LARGE(rng, k) with k > COUNT(rng)Masks invalid rank k or negative varianceIF(k > COUNT(rng), "Rank Exceeds Count", LARGE(rng, k))
#REFINDEX(rng, r) where r < 1 or r > ROWS(rng)Returns 0 as if row existedIF(OR(r < 1, r > ROWS(rng)), "Row Out of Bounds", INDEX(rng, r))
#NAME?Misspelled function VLLOKUP or unquoted text ActiveHides broken formula syntaxFix 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.

Further reading