#NUM! Error in Excel — Every Cause, Every Fix
The #NUM! error means Excel hit a number it can't work with — too big, too small, mathematically impossible, or a calculation that won't converge. Here's every root cause, five real fix scenarios, and a prevention checklist that works.
What it means: A formula or function has produced a numeric value that is invalid — either outside Excel's supported range, mathematically undefined, or the result of an iterative calculation that failed to reach a stable answer.
What the #NUM! error actually means
Excel raises #NUM! whenever a formula tries to produce a number that either can't exist in Excel's numeric system or can't be computed reliably. Unlike #VALUE! (which is about wrong data types) or #DIV/0! (which is about a specific division problem), #NUM! is about the number itself being impossible or unreachable.
The three broad categories are:
- Out of range — the result is bigger than
9.99999999999999E+307or smaller than-9.99999999999999E+307 - Mathematically undefined — square root of a negative number, logarithm of zero or a negative, a factorial of a negative number, and similar
- Non-convergent iteration — functions like
IRR,RATE, andMIRRthat solve equations iteratively and give up when they can't find an answer
#VALUE! is "Excel doesn't understand what you gave it," then #NUM! is "Excel understands you perfectly — but the answer doesn't exist, or it can't get there." That distinction matters when debugging.
The 6 root causes of #NUM!
The result is too big or too small for Excel
Excel supports numbers roughly between 1E-307 and 1E+307. Anything outside that range — like =POWER(10, 400) or =FACT(200) — returns #NUM!.
Common culprits: exponents on large numbers, factorials above 170, compound-interest formulas over many decades with high rates, matrix determinants of large matrices.
A mathematically impossible operation
=SQRT(-1), =LN(0), =LOG(-5), =ASIN(2), =FACT(-3) — all return #NUM! because the operation is undefined in real-number math.
Fix pattern: use ABS to force positive inputs, or use IF to check the input's sign before running the function.
Iterative functions can't converge on an answer
IRR, MIRR, RATE, XIRR, and YIELD use iteration to find an unknown rate. If they can't reach a stable answer in 20 iterations (Excel's default), they return #NUM!.
This usually happens when all cash flows are the same sign (impossible IRR), when the answer is very far from Excel's default guess of 10%, or when the cash flow series has multiple valid answers.
Wrong sign convention in financial functions
Excel's financial functions treat outflows as negative and inflows as positive. If you enter a loan's payment (PMT) as positive while the present value is also positive, NPER or RATE may return #NUM! because the resulting equation has no real solution.
Arguments outside a function's valid range
=LARGE(A1:A10, 15) when the range only has 10 values → #NUM!. Same for SMALL. Also =DATE(1899, 12, 31) — Excel doesn't recognize dates before 1900 (or 1904 on Mac's alternate date system).
Result is a valid number, but not representable
Rare, but shows up in scientific work: results that are so close to zero they underflow, or complex-number results in operations that only return reals. Excel can compute the intermediate step but not deliver a final value.
5 real-world fix scenarios
You're computing standard deviations manually and one term goes slightly negative due to rounding.
=SQRT(B2 - B3)When B2 - B3 ends up negative (even by 0.00001), you get #NUM!.
Guard with MAX to floor the input at zero — or use ABS if a small negative really means "essentially zero":
This treats tiny negatives as zero rather than crashing the workbook.
You've laid out a cash-flow series and IRR returns #NUM!:
Excel's default guess of 10% is too far from the actual IRR.
Provide a better initial guess as the second argument:
=IRR(A2:A10, -0.2)If the IRR might be very negative (a bad investment), start there. If it's very high, guess 0.5 or 1. Try 2-3 different guesses to check for multiple valid IRRs.
You're pulling the top 10 sales values with LARGE, but the source range sometimes has fewer than 10 rows:
=LARGE(Sales[Amount], 10)Wrap in IFERROR — or better, cap k at the actual count:
This asks for "position 10 or the last position, whichever comes first" — no more #NUM! on short lists.
You're calculating how many months to pay off a loan and get #NUM!:
Both the payment (500) and loan (20000) are positive. Excel can't solve — the debt would grow forever.
Payments out of your pocket must be negative:
=NPER(0.05/12, -500, 20000)Now Excel understands: you receive $20,000, then pay $500 each month, and it can compute how long that takes (about 44 months here).
A compound-interest calculation over 500 years:
=POWER(1.08, 500) * 1000Result is around 4.4E+19 times $1,000 = fine. But push it further and you'll hit #NUM!.
For extreme exponents, work in logarithms and format the display separately, or cap the exponent at a sensible business limit:
=IF(B2 > 700, "Exceeds Excel range", POWER(1.08, B2) * 1000)Guard the input rather than letting the formula crash silently.
Prevention checklist
- Always sign-check financial inputs — outflows negative, inflows positive, without exception.
- Provide a guess argument for IRR and RATE when working with unusual cash flows or interest rates outside 5-15%.
- Wrap SQRT, LN, LOG in guards — either
MAX(0, ...),ABS(...), or anIFcheck. - Cap k in LARGE and SMALL using
MIN(k, COUNT(range)). - Watch for compound growth over long horizons — 100+ years at 10% will hit Excel's range limit.
- Use IFERROR sparingly — it hides
#NUM!but also hides real bugs. Prefer input guards over blanket error suppression. - For dates before 1900, use text and helper columns — Excel's date system starts January 1, 1900 (or 1904 on some Macs).
Version compatibility
The #NUM! error itself has existed since Excel 1.0 and behaves identically across every version. What differs is the functions that produce it — newer ones like PDURATION, RRI, and dynamic-array financial calculations only exist in modern Excel.
Download the practice workbook
Every scenario above, laid out as broken/fixed pairs so you can reproduce and repair each #NUM! in real Excel.
Related errors
Frequently asked questions
Why do I get #NUM! when my IRR formula worked yesterday?
The cash-flow series probably changed. IRR needs at least one negative and one positive value, and its convergence depends on the shape of the series. Small edits — like changing one positive cash flow to negative — can flip the equation from solvable to unsolvable. Try providing a guess argument like =IRR(range, 0.05) or =IRR(range, -0.1).
What's the biggest number Excel can handle before returning #NUM!?
The theoretical maximum is 9.99999999999999E+307. The smallest positive number is about 2.2251E-308. Anything past those limits — either too big or too close to zero — returns #NUM!. This applies to both stored values and intermediate calculation results.
Does IFERROR fix #NUM! errors?
It suppresses them, which isn't the same as fixing them. =IFERROR(SQRT(A1), 0) will hide any #NUM! and return 0 instead — but you'll never know something's wrong. Better to fix the input: =SQRT(MAX(0, A1)). Use IFERROR only as a last resort for display purposes in dashboards.
Why does FACT(170) work but FACT(171) return #NUM!?
Because 170! is around 7.26E+306, which fits inside Excel's range. 171! would be about 1.24E+309, which exceeds the maximum representable number. This is a hard limit — no workaround exists inside standard Excel without moving to logarithmic space or using Python via PY() in Excel 365.
Can #NUM! appear from a lookup or reference function?
Rarely from VLOOKUP, XLOOKUP, or INDEX/MATCH directly — those usually return #N/A when they can't find something. But LARGE, SMALL, and RANK do return #NUM! when the position argument is out of bounds or when the input array is empty.
Does Google Sheets treat #NUM! the same way?
Almost identically. The same causes trigger it — impossible math, out-of-range results, non-convergent iterations. Google Sheets uses slightly different numeric limits internally but the practical behavior for typical spreadsheet use is the same. Fix patterns from this guide work in both.
Why do I get #NUM! from DATE() for old dates?
Excel's default date system starts January 1, 1900. Any date before that — =DATE(1850, 6, 15) for example — returns #NUM!. On Mac, some workbooks use the 1904 date system, which pushes the lower limit even later. For historical dates, store them as text and use helper columns for any calculations.
Fix errors before they hit — with the Sheets & Cells AI Add-in
Our Excel Add-in scans your workbook for high-risk formula patterns — including the exact input conditions that produce #NUM! — and suggests guards before the errors ever appear. Right inside Excel. No copy-paste to a chatbot.