Fix the #NUM! Error

Excel & Google Sheets

How it works

#NUM! means the math itself broke: the formula asked for a number that can't exist or can't be represented. The classic causes are taking the square root or logarithm of a negative number, a result beyond the spreadsheet's limits (=1000^1000 overflows), an iterative function like IRR or RATE failing to converge on an answer, or date arithmetic that runs backwards — DATEDIF with an end date before the start date returns #NUM!. So the fix starts with the input, not the formula: find which value is negative, swapped, or out of range and correct it at the source. Wrap in IFERROR only after you've confirmed the bad inputs are legitimate — for example, negative variances where a square root genuinely doesn't apply — because a blanket IFERROR will also hide the rows you'd want to catch.

SQRT(A2)

The original calculation. It returns #NUM! whenever A2 is negative, because a real square root of a negative number doesn't exist.

IFERROR(…, "Check input")

Shows a pointed message instead of #NUM! — but only add this after confirming negative inputs are expected in your data.

IRR / RATE guesses

If a financial function won't converge, supply a starting guess: =IRR(B2:B10,0.1) often resolves #NUM! instantly.

When to use it

Diagnose #NUM! whenever root, log, or power formulas hit negative or huge values, when IRR or RATE won't converge, or when date subtraction has start and end reversed.

Common mistakes

• ✕Blanketing every row with IFERROR before diagnosing.

  ✓If A2 should never be negative, #NUM! just caught a data-entry error. Investigate first; suppress only inputs you've confirmed are legitimate.

• ✕Swapped start and end dates in DATEDIF.

  ✓=DATEDIF(B2,A2,"D") errors when B2 is the later date. Put the earlier date first: =DATEDIF(A2,B2,"D").

• ✕Treating a non-converging IRR as broken data.

  ✓IRR needs at least one negative and one positive cash flow, and sometimes a guess. Check the signs, then try =IRR(range,0.1).