Skip to content
ArumSoft Learning
KO
    #NUM!Excel

    How to fix the #NUM! error in Excel

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerCheck the inputs: a negative number under SQRT or LOG, a start date after the end date in DATEDIF, or a list of cash flows for IRR that has no negative value.

    Causes and fixes

    1. A function received a negative number or zero it can't use
    2. DATEDIF has the start date after the end date
    3. IRR or RATE can't find an answer
    4. LARGE, SMALL or INDEX asks for a value that isn't there
    5. 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.