Excel guide · 4 min read

#NUM! error in Excel: causes and fixes

Q: What does #NUM! mean in Excel?

It means the formula produced an invalid number: a mathematically impossible operation, a result outside Excel's range, or an iterative function that failed to converge.

Q: How is #NUM! different from #VALUE!?

#VALUE! means an argument is the wrong type, such as text where a number is needed. #NUM! means the type is fine but the number itself does not work for that calculation.

Q: Why does IRR return #NUM!?

Either the cash flows do not include both negative and positive values, or Excel could not converge within 20 tries. Check the signs and add a guess like 0.1 as the second argument.

Q: How do I replace #NUM! with 0?

Use =IFERROR(your_formula,0). Do this only after confirming the error comes from expected data, not from a broken formula.

Quick answer#NUM! means Excel cannot produce a valid number: you asked for something impossible (the square root of a negative, a DATEDIF where the start date is after the end date), the result is beyond what Excel can store, or an iterative function like IRR could not find an answer. Fix the input, reorder the dates, or give IRR a starting guess.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D
1	Item	Value	Start	End
2	Variance	-16	2026-03-01	2026-01-15
3	Growth rate	1.08
4	Cube root of	-27

Square root of a negative number

SQRT only works on zero or positive numbers. B2 is -16, so =SQRT(B2) returns #NUM!. This usually happens when a value you expected to be positive went negative, such as a difference or a variance calculated the wrong way round.

Decide what the negative means. If it is a data mistake, fix the data. If only the size matters, wrap the input in ABS. If negatives are valid but should not be square-rooted, catch them with IF.

=IF(B2<0,"Negative input",SQRT(B2))

Result: Negative input

B2 is negative, so the formula shows a clear message instead of #NUM!.

DATEDIF with the dates the wrong way round

DATEDIF needs the earlier date first. Here the start (1 March) is after the end (15 January), so =DATEDIF(C2,D2,"d") returns #NUM!. This is common when two date columns get swapped or a project "end" is a planned date that has not been updated.

If you only need the gap and not the direction, put MIN and MAX around the two dates so the earlier one always comes first. If the direction matters, simply subtract: =D2-C2 returns a negative number of days instead of an error.

=DATEDIF(MIN(C2,D2),MAX(C2,D2),"d")

Result: 45

MIN and MAX put 15 January first, so DATEDIF returns 45 days.

A result too big for Excel

Excel can store numbers up to about 1.8 x 10^308. Anything larger returns #NUM!. You rarely hit this with real money, but you can with compounding: =B3^10000 (8% growth over 10,000 periods) overflows.

If you see this, the formula is almost always pointing at the wrong cell or using the wrong units (days instead of years, a percentage typed as 8 instead of 8%).

=B3^10

Result: 2.1589249973

8% growth compounded over 10 periods is about 2.159, well inside Excel's limit.

Fractional powers of a negative number

=POWER(-27,1/3) looks like it should return -3, but Excel returns #NUM! because it calculates fractional powers in a way that does not allow negative bases. The same happens with =(-27)^(1/3).

For odd roots of negative numbers, take the root of the absolute value and put the sign back.

=-POWER(ABS(B4),1/3)

Result: -3

The cube root of 27 is 3, and putting the minus back gives -3.

IRR or RATE cannot find an answer

IRR, XIRR and RATE work by guessing an answer and refining it up to 20 times. If they do not settle within that, they return #NUM!. It also happens when the cash flows never change sign: IRR needs at least one negative value (money out) and one positive value (money in).

First check the signs of your cash flows. Then supply the optional guess argument, for example =IRR(B2:B10,0.1) for 10% or =IRR(B2:B10,-0.5) for a losing investment. A guess close to the real answer helps Excel converge.

Make sure the first cash flow (the investment) is negative and later returns are positive.
Add a guess as the second argument, e.g. =IRR(B2:B10,0.1).
Still #NUM!? Try a few different guesses (0, 0.2, -0.2). For irregular dates use XIRR, which also takes a guess.

Asking for an item that does not exist

SMALL and LARGE return #NUM! when the position you ask for is outside the list: the 4th smallest of three numbers, or the 0th largest. This shows up when a "top 5" table runs on a list that only has 3 entries.

Wrap it so short lists show a blank: =IFERROR(LARGE($B$2:$B$4,ROWS($1:4)),"").

Common mistakes

Hiding #NUM! with IFERROR straight away

IFERROR also hides typos and wrong ranges. Find out which input is impossible first, then decide whether a message is right.

Typing percentages as whole numbers

A rate of 8 means 800%. Type 8% or 0.08, otherwise growth formulas explode into huge numbers or #NUM!.

Using DATEDIF when a simple subtraction would do

For a plain number of days, =End-Start never returns #NUM! and keeps the sign, which tells you the dates are backwards.

FAQ

What does #NUM! mean in Excel?

It means the formula produced an invalid number: a mathematically impossible operation, a result outside Excel's range, or an iterative function that failed to converge.

How is #NUM! different from #VALUE!?

#VALUE! means an argument is the wrong type, such as text where a number is needed. #NUM! means the type is fine but the number itself does not work for that calculation.

Why does IRR return #NUM!?

Either the cash flows do not include both negative and positive values, or Excel could not converge within 20 tries. Check the signs and add a guess like 0.1 as the second argument.

How do I replace #NUM! with 0?

Use =IFERROR(your_formula,0). Do this only after confirming the error comes from expected data, not from a broken formula.

Functions used

Related guides

Updated October 31, 2026