The #NUM! error means a formula produced a number that Excel can't handle — usually a number too large, an impossible mathematical operation, or an iterative calculation that won't converge. Here's how to diagnose and fix it.
What #NUM! means
#NUM! is a "numeric problem" error. The formula is valid, but the math leads somewhere Excel can't represent or compute: a result outside Excel's number range, an operation with no real answer, or a function that can't find a solution.
The common causes
Impossible math operations. Some calculations have no real-number answer:
- Square root of a negative number:
=SQRT(-4)returns #NUM!. - Certain powers of negatives, or LOG of zero or a negative number.
The fix: check whether your inputs can go negative or zero where the math requires positives. Guard against it, for example:
=IF(A1>=0, SQRT(A1), "n/a")
Numbers too large or too small. Excel has limits (roughly 1.8 × 10^308 at the top). A calculation that overflows this — like a huge factorial or exponential — returns #NUM!. =FACT(200) is far too large, for instance. There's no fix except avoiding the overflow; the number genuinely can't be represented.
Iterative functions that can't converge. Functions like IRR, RATE, and XIRR search for an answer by iteration. If they can't find one — often because the data has no valid solution or the starting guess is poor — they return #NUM!.
The fix: provide a guess argument to help them start. Many of these functions accept an optional guess:
=IRR(values, 0.1)
Starting near a plausible answer (here 10%) often lets the function converge. Also check that your cash-flow data actually has a valid solution — IRR needs at least one positive and one negative value.
Invalid function arguments. Passing a nonsensical argument to a function — like a negative value where only positives make sense — can trigger #NUM!. Check each argument against what the function expects.
How to diagnose
- Identify which function or operation is producing the error.
- Ask: could this be doing impossible math (negative square root, log of non-positive)?
- Could the result be astronomically large?
- Is it an iterative financial function that might not converge?
- Check the arguments for values outside the function's valid range.
Handling it gracefully
Once you understand the cause, either fix the input or guard the formula:
=IFERROR(SQRT(A1), "Invalid")
But as always, only suppress the error after you understand why it's occurring — #NUM! often signals genuinely invalid data or an impossible request, not just a cosmetic issue.
The takeaway
#NUM! means the math went somewhere Excel can't follow — an impossible operation, a number too big, or a calculation that won't converge. Trace which operation caused it, check whether your inputs are pushing the math out of bounds, and either correct the data or guard the formula. It's less common than other errors but usually points to a real problem worth understanding.