A #NUM! error means Excel received numeric inputs but could not produce a valid number. The calculation may be mathematically impossible, too large to display, or unable to converge on an answer.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when a formula returns #NUM! instead of a result. This differs from #VALUE!, which usually means the wrong kind of input, such as text where a number was expected.
Find the impossible calculation
Start with math that cannot return a real number.
- Check for square roots of negative numbers, such as =SQRT(-4).
- If the sign does not matter, wrap the value in ABS, such as =SQRT(ABS(A2)).
- Check logarithms, powers, or other functions receiving values outside their allowed range.
- Look for runaway formulas that accidentally reference whole rows or columns and create a result too large for Excel.
- Check financial functions such as IRR, RATE, and XIRR if they cannot find a result.
Fix formulas that cannot converge
Financial functions may need a better starting guess.
- Add the optional guess argument close to the expected answer, such as =IRR(A2:A10,0.1).
- Try a few guesses such as 0.01, 0.1, or 0.5 if you do not know where the answer should land.
- Confirm the cash flows or inputs make mathematical sense for the function.
- Check that the formula references only the intended range, not an entire column by mistake.
More control
Distinguish #NUM from #VALUE
#VALUE means Excel cannot use the input type. #NUM means the input is numeric but the math or result is invalid. That distinction narrows the search quickly.
Check circular references
Some circular calculations can surface as #NUM, especially with iterative formulas. Open Formulas > Error Checking > Circular References to see whether Excel lists one.
Use Evaluate Formula
Open Formulas > Evaluate Formula to step through a complex expression and identify exactly which part becomes #NUM.
Sources
- Microsoft Support – Correct a #NUM! error (2025)
- Microsoft Support – Detect errors in formulas (2025)
- Microsoft Support – IRR function (2025)