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

  1. 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.

    ABC
    1Number
    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.

    ABC
    1Number
    2-164

    In C2: =SQRT(ABS(A2)) → 4

  2. An 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.

    ABC
    1Score
    240#NUM!
    355

    In C2: =PERCENTILE(A2:A3, 90) → #NUM!

    How to fix it. Write the percentage as a decimal, or with a percent sign: 0.9 or 90%.

    ABC
    1Score
    24053.5
    355

    In C2: =PERCENTILE(A2:A3, 0.9) → 53.5

  3. Dates in the wrong order

    DATEDIF counts from the first date to the second, and the first must be the earlier one.

    ABCD
    1StartEnd
    22026-03-152026-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.

    ABCD
    1StartEnd
    22026-03-152026-03-0114

    In D2: =DATEDIF(MIN(A2:B2), MAX(A2:B2), "D") → 14

  4. A number too large for Excel

    Excel holds numbers up to about 1.8 followed by 308 zeros. Anything beyond that is #NUM!.

    A
    1Result
    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).

  5. 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

Functions involved

Other errors