Excel errors / #NUM!
#NUM! error in Excel
The calculation has no valid number as its answer.
#NUM! means the formula was given numbers it cannot work with, or produced one that Excel cannot hold. The square root of a negative number, a percentile of 90 where 0.9 was meant, a date range that runs backwards.
Unlike #VALUE!, the inputs are numbers. They are just outside what the function accepts.
Have a formula that shows #NUM!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.
5 causes of #NUM!, and how to fix each
A calculation with no real answer
No real number multiplied by itself gives -16, so its square root does not exist. Logarithms of zero and of negative numbers fail the same way.
A B C 1 Number 2 -16 #NUM! In C2:
=SQRT(A2)→ #NUM!How to fix it. Check why the number is negative. If only its size matters, take the absolute value first.
A B C 1 Number 2 -16 4 In C2:
=SQRT(ABS(A2))→ 4An argument outside the range the function accepts
PERCENTILE wants its percentage as a number between 0 and 1. 90 is not a valid percentage to it; 0.9 is.
A B C 1 Score 2 40 #NUM! 3 55 In C2:
=PERCENTILE(A2:A3, 90)→ #NUM!How to fix it. Write the percentage as a decimal, or with a percent sign:
0.9or90%.A B C 1 Score 2 40 53.5 3 55 In C2:
=PERCENTILE(A2:A3, 0.9)→ 53.5Dates in the wrong order
DATEDIF counts from the first date to the second, and the first must be the earlier one.
A B C D 1 Start End 2 2026-03-15 2026-03-01 #NUM! In D2:
=DATEDIF(A2, B2, "D")→ #NUM!How to fix it. Swap the two dates. If either may come first, let MIN and MAX put them in order.
A B C D 1 Start End 2 2026-03-15 2026-03-01 14 In D2:
=DATEDIF(MIN(A2:B2), MAX(A2:B2), "D")→ 14A number too large for Excel
Excel holds numbers up to about 1.8 followed by 308 zeros. Anything beyond that is #NUM!.
A 1 Result 2 #NUM! In A2:
=10^400→ #NUM!How to fix it. Numbers this size are rarely meant. Check for an exponent or a factorial that has run away, such as
=FACT(200).IRR or RATE cannot find an answer
IRR and RATE search for their answer by trial and error. They give up with #NUM! when there is none to find, which for IRR means the cash flows are all positive or all negative: there must be at least one of each.
How to fix it. Check that money paid out is negative and money received is positive. If the signs are right, give the function a starting guess as its last argument, such as 0.1.
Common questions
How do I fix #NUM! in Excel?
Look at the numbers going into the function and compare them with what it accepts: no negative numbers for SQRT, a value between 0 and 1 for PERCENTILE, the earlier date first for DATEDIF. The function reference lists the limits for each argument.
Why does IRR return #NUM!?
Most often because the cash flows do not change sign. IRR needs at least one negative value (the investment) and one positive value (a return). It also fails when it cannot settle on an answer within 20 tries; a guess as the second argument helps.
Practise it
- Compare investments with NPV and IRR
- Date maths: gaps, renewals and month ends
- Percentiles and quartiles
Functions involved
- SQRT: Square root.
- PERCENTILE: The value below which a given percentage of the data falls.
- DATEDIF: The difference between two dates in years, months or days.
- IRR: Internal rate of return for a series of cash flows.
- RATE: The interest rate per period of a loan or investment.
- ABS: The absolute value: the number without its sign.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #SPILL!: A formula that returns several values has no room to put them.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.