#NUM! error: what it means and how to fix it
A number in the formula (or its result) isn't valid for that calculation.
#NUM! means a calculation got a number it can't use, couldn't find an answer, or produced a result too large or too small to show.
Common causes, ranked
- 1
A number the function can't accept
For example the square root of a negative number. Microsoft also lists numbers typed with a format the argument doesn't accept, such as
$1,000instead of1000.Fix: Check the inputs make sense for the function.
Shows the error
=SQRT(-16)Fixed
=SQRT(ABS(-16)) - 2
IRR or RATE can't find a result
These functions search for an answer by trying values, and give up after a number of tries.
Fix: Give IRR a different guess as its last argument. In Excel you can also let it try more times: File › Options › Formulas › Enable iterative calculation, then raise Maximum Iterations.
Shows the error
=IRR(B2:B6)Fixed
=IRR(B2:B6,0.1) - 3
A result too big or too small
Excel has a limit on how large or small a number it can show.
Fix: Change the formula so the result stays in range.
Shows the error
=10^400
Should you hide it?
Fix the inputs rather than hiding #NUM!; it's telling you the calculation has no valid answer.
Careful: If you must hide it, use a visible message, not 0: a zero rate of return is a real-looking (and wrong) answer.
Excel vs Google Sheets
#NUM! is an error value in both Excel and Google Sheets.
Sources
Practise this in 5 minutes
Fixing #NUM! is taught in Lesson 48: Savings, NPV and IRR (Unit 14, Money and finance), on a live sheet with instant feedback. Pro
Open “Savings, NPV and IRR”Need a quick reference? The formula cheat sheet is free to read and print.