Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #NUM! error
Excel & Google SheetsWhat #NUM! means
#NUM! means a formula tried to produce a number that is invalid or impossible to represent — like the square root of a negative number, or a value too large for the spreadsheet to hold.
Common causes
- A math function received an impossible argument, e.g. SQRT of a negative number.
- An iterative function (like IRR or RATE) could not converge on an answer.
- A calculation produced a number larger than the spreadsheet can store.
- A function argument is outside its allowed range.
Example fix
Broken
=SQRT(A2)Fixed
=IF(A2>=0, SQRT(A2), "n/a")SQRT errors on negative input. The IF guard returns a safe value instead.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Check the inputs to any math function for impossible values, such as negatives passed to SQRT or LOG.
- 2For SQRT of a possibly-negative value, guard it: =IF(A2>=0, SQRT(A2), "n/a").
- 3For IRR or RATE, supply a reasonable guess argument to help the function converge.
- 4Verify the result is not astronomically large because of an unintended multiplication or exponent.
- 5Confirm each argument falls within the function’s documented limits.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every #NUM! and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Why does IRR return #NUM!?
IRR solves iteratively and can fail to converge, especially with unusual cash flows. Provide a guess argument, e.g. =IRR(A2:A20, 0.1).
Can a date cause #NUM!?
Yes — dates before the year 1900 (Excel) are out of range and produce #NUM! in date math.