The #NUM! error
The calculation is impossible, or its result falls outside Excel's limits: the square root of a negative number, an end date before the start date, a rate that does not converge.
What it means
#NUM! means a number in the formula, argument or result, is not valid.
Where it comes from
- An impossible calculation:
=SQRT(-4),=LOG(0). - DATEDIF with dates reversed: the start date after the end date.
- IRR, RATE or XIRR finding no solution in twenty attempts.
- A result out of range: beyond about 1E+308.
- An argument outside its domain: LARGE(A:A,50) on a range of 10 values.
Fixing it
- For DATEDIF: check the order of the dates,
=IF(A2<=B2,DATEDIF(A2,B2,"m"),""). - For IRR and RATE: give a starting guess as the last argument,
=IRR(B2:B10,0.1). - Check negative or zero values passed to roots and logarithms.
Tools that help
- Explain a formula: says what each argument expects.
- Find the right formula: the gap between two dates, in the right order.
Frequently asked questions
- Why does DATEDIF return #NUM!?
- The start date is after the end date. DATEDIF cannot count backwards.