How to fix the #NUM! error in Excel
Causes and fixes
- A function received a negative number or zero it can't use
- DATEDIF has the start date after the end date
- IRR or RATE can't find an answer
- LARGE, SMALL or INDEX asks for a value that isn't there
- The result is too large or too small
1A function received a negative number or zero it can't use
=SQRT(-4) and =LOG(0) have no answer in Excel, so they return #NUM!.
Fix
Check the input first, or use its absolute value if that's what you meant:
=IF(A2<0,"",SQRT(A2))
=SQRT(ABS(A2))2DATEDIF has the start date after the end date
DATEDIF returns #NUM! when the first date is later than the second.
Fix
Swap the dates, or put the earlier one first automatically:
=DATEDIF(MIN(A2,B2),MAX(A2,B2),"y")3IRR or RATE can't find an answer
IRR needs at least one negative value (money out) and one positive value (money in). It also tries a limited number of times, and returns #NUM! if it doesn't get close enough.
How to recognise it
All the cash flows have the same sign, or the result should be unusual, such as a big loss.
Fix
Make the initial investment negative, then give IRR a starting guess near the answer you expect:
=IRR(B2:B10,-0.1)RATE takes a guess the same way, as its sixth argument.
4LARGE, SMALL or INDEX asks for a value that isn't there
=LARGE(B2:B6,6) asks for the 6th largest of 5 numbers. A position of 0 also returns #NUM!.
Fix
Check the position against the count: =IF(D2<=COUNT(B2:B6),LARGE(B2:B6,D2),"").
5The result is too large or too small
Excel numbers go up to about 1E+308. =10^309 or =FACT(171) returns #NUM!.
Fix
Check the formula for a runaway calculation, such as a growth rate entered as 50 instead of 50%.
Still stuck?
- Select the cell and use Formulas > Evaluate Formula to see which step returns #NUM!.
- If the formula refers to itself and iterative calculation is on, check File > Options > Formulas.
- Percentages typed as whole numbers (5 instead of 5%) are a common cause in finance formulas.