How to Fix #NUM! Errors in Excel: Complete Guide
The #NUM! error means Excel received a numeric value it cannot work with — either mathematically impossible, out of range, or a function that couldn't converge. The fix depends on which of the three causes is responsible.
Quick Fix
#NUM! has three distinct causes — match the fix to your situation:
=IF(A1>=0, SQRT(A1), "") ' guard SQRT/LOG from negative inputs =IRR(A1:A10, 0.1) ' add a guess to help IRR converge =IFERROR(formula, "") ' suppress if result overflows range
Find all #NUM! errors automatically — upload your spreadsheet and ExcelErrorFinder will locate every numeric error across all sheets with full formula context.
Free Audit →What Is a #NUM! Error?
A #NUM! error (numeric error) appears when a formula receives a numeric argument that is mathematically invalid or produces a result outside the range Excel can represent. Unlike most Excel errors, #NUM! usually means the formula itself is correct — it's the input data or the mathematical constraints of the function that are the problem.
There are three distinct causes, and the fix for each is completely different. For the official reference, see Microsoft's #NUM! error documentation.
Cause 1: Mathematically Invalid Argument
Certain functions require their arguments to meet mathematical constraints. When the input violates those constraints, the function returns #NUM!:
- SQRT of a negative number:
=SQRT(-4)→#NUM!— square roots of negative numbers don't exist in real-number arithmetic. - LOG or LOG10 of zero or a negative:
=LOG(0)or=LOG(-5)→#NUM!— logarithms are only defined for positive numbers. - POWER with fractional exponent on a negative base:
=POWER(-8, 1/3)→#NUM!— even though the cube root of -8 is -2, Excel does not support this for fractional exponents. - Negative arguments to functions expecting counts:
=FACT(-1)→#NUM!— factorial is only defined for non-negative integers.
Fix: Guard the Input
=IF(A1 >= 0, SQRT(A1), "Invalid")
=IF(A1 > 0, LOG(A1), "")
=IFERROR(SQRT(A1), 0)If the negative value is a data entry error (a cost entered as a positive number instead of negative, for example), fix the source data rather than wrapping the formula.
Cause 2: Result Too Large or Too Small for Excel
Excel supports numbers between approximately -1×10^308 and 1×10^308. Calculations that produce results outside this range return #NUM!. The most common way to hit this limit is with exponential growth formulas or compounding interest over very long periods:
=POWER(1.1, 10000) '→ #NUM! — result exceeds Excel's maximumThis is rare in everyday spreadsheets but common in financial models that accidentally use daily rates in formulas expecting annual rates, producing astronomically large intermediate values.
Fix: Check Your Rate and Period Units
Verify that the rate and period arguments match. A monthly interest rate of 0.5% compounded over 1,000 months is valid; the same rate applied to 1,000 years (12,000 periods) may overflow. Reconsider whether the model's scope is realistic.
Cause 3: Iterative Functions That Cannot Converge
Functions like IRR, XIRR, RATE, and MIRR use iterative algorithms — they try different values until they find one that satisfies the equation. When no solution exists, or the function cannot find it within 20 iterations, it returns #NUM!.
IRR and XIRR
IRR requires at least one sign change in the cash flow series — at least one negative (outflow) and one positive (inflow). If all values are negative, or all are positive, no internal rate of return exists and IRR returns #NUM!.
=IRR(A1:A10) ' May fail with unusual cash flow patterns
=IRR(A1:A10, 0.1) ' Provide a starting guess to help convergence
=XIRR(A1:A10, B1:B10, 0.1) ' XIRR with dates and a guessCommon IRR fixes: ensure the first cash flow is negative (initial investment), provide a guess argument, and verify the cash flow series contains both negative and positive values.
RATE Function
=RATE(nper, pmt, pv) returns #NUM! when the combination of arguments is financially impossible (for example, a loan that can never be paid off with the specified payment amount). Check that the payment amount is large enough to cover at least the interest on the principal, and that nper, pmt, and pv signs are correct (payment should be negative if present value is positive).
How to Find All #NUM! Errors
Use Ctrl+F and search for #NUM!, or press F5 → Special → Formulas → Errors to select every error cell on the current sheet. The ExcelErrorFinder auditor scans every sheet simultaneously and reports each error with its formula and cell address.
Frequently Asked Questions
Can I suppress #NUM! with IFERROR?
Yes — =IFERROR(IRR(A1:A10), "No solution") will return "No solution" instead of the error. However, for financial functions like IRR and RATE, a #NUM! almost always means the underlying data has a problem worth investigating rather than suppressing.
Why does #NUM! appear in a date formula?
Functions like DATE(), EDATE(), or WORKDAY() return #NUM! if the result would be a date before January 1, 1900 (Excel's minimum date) or after December 31, 9999. Check that subtraction operations on dates don't produce negative date serial numbers.
Frequently Asked Questions
What does #NUM! mean in Excel?
#NUM! means a formula received a numeric argument that is mathematically invalid — for example, the square root of a negative number, a number outside Excel's supported range (roughly ±1×10^308), or an iterative function like IRR that could not converge to a solution.
Why does IRR return #NUM! even when my cash flows look correct?
IRR requires at least one sign change in the cash flow series — at least one negative value followed by positive values (or vice versa). If all values are the same sign, IRR cannot find a rate and returns #NUM!. Also try providing a guess argument: =IRR(A1:A10, 0.1) — the default guess of 10% sometimes fails to converge for unusual cash flow patterns.
How do I fix #NUM! in SQRT or LOG?
SQRT and LOG return #NUM! when the argument is negative. For SQRT, use =SQRT(ABS(A1)) to take the square root of the absolute value, or add a check: =IF(A1>=0, SQRT(A1), "Invalid"). For LOG with a negative argument, verify that your input data contains the correct values — negative numbers in a log formula almost always indicate a data entry error.