Spreadsheet error
How to fix the #NUM! error
TL;DR
#NUM! means a formula received a numerically invalid argument — negative number under a square root, too-large result, or iterative function that did not converge.
What it means
The #NUM! error is returned when a formula has a problem with numbers: the value is out of the legal range for the function, or the function cannot compute a result (e.g., IRR that does not converge).
Common causes
- •SQRT on a negative number.
- •LOG on zero or negative.
- •IRR / RATE / XIRR that does not converge in 20 iterations.
- •DATE() with year outside 1900–9999.
- •Result exceeds 1.79E+308 (Excel max) or is below Excel precision.
How to fix it
1.Guard the domain
- For SQRT: =IF(A2<0, 0, SQRT(A2)) or =SQRT(ABS(A2)) if absolute value is fine.
- For LOG: =IF(A2<=0, "", LOG(A2)).
2.Provide a guess for IRR/RATE
- IRR accepts a guess as a second argument.
- =IRR(values, 0.1) — starts iteration closer to the expected answer.
- Or use XIRR which handles irregular intervals better.
3.Use IFERROR as a safety net
- =IFERROR(IRR(values), "No solution")
- But prefer explicit domain guards above — they tell you which case failed.
Example
Broken
=SQRT(A2)Fixed=IF(A2<0, 0, SQRT(A2))When A2 is negative, SQRT returns #NUM!. Guard by returning 0 (or ABS) for negatives.
Prevention
- Validate inputs at ingestion (no negatives where physically impossible).
- Use domain-specific defaults for edge cases (0 vs N/A vs blank).
- Provide IRR/RATE with a reasonable guess to help convergence.
Related errors
Let FormulaPad debug it for you
Paste your broken formula and the error message — get a diff and an explanation in seconds.
Debug a formula