#NUM! Error in Google Sheets
What it means: The calculation is a valid type but the number is impossible or too large — like the square root of a negative value.
Quick fix
=IFERROR(SQRT(A2), "")
Trap impossible results (like SQRT of a negative) so the sheet stays clean.
Why you see #NUM! — and how to fix each cause
1. Impossible math operation
=SQRT(-4) has no real answer, so Sheets returns #NUM!.
Fix: Guard the input, e.g. only take SQRT of non-negative values.
=IF(A2>=0, SQRT(A2), "")
2. A number too large to represent
Extremely large results, such as =10^1000, exceed what Sheets can store.
Fix: Scale the calculation down or work with logarithms.
3. A function argument is out of its allowed range
Functions like DATE, IRR or some financial formulas return #NUM! when given values outside what they accept.
Fix: Check the function's expected argument ranges and correct the inputs.
Before and after
| Broken | Working |
|---|---|
=SQRT(A2) (A2 is -9) |
=IF(A2>=0, SQRT(A2), "") |
Checking the input before the operation avoids the impossible calculation.
How to stop it happening again
Validate inputs before feeding them into math functions — use IF guards for negatives, keep numbers within a sane range, and remember iterative functions (IRR, RATE) may need a starting guess to converge.
FAQ
What's the difference between #NUM! and #VALUE!?
#VALUE! is the wrong type of data (text instead of number). #NUM! is the right type but an impossible or out-of-range number.
Why does IRR return #NUM!?
IRR is iterative and may fail to converge. Provide a guess argument, e.g. =IRR(range, 0.1).
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas