How to fix the #NUM! error

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. In general, the fixing the #NUM! error is a matter of adjusting inputs as required to make a calculation possible again. See below for more information.

Explanation

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas that return the #NUM error. In general, the fixing the #NUM! error is a matter of adjusting inputs as required to make a calculation possible again.

Example #1 - Number too big or small

Excel has limits on the smallest and largest numbers you can use. If you try to work with numbers outside this range, you will receive the #NUM error. For example, raising 5 to the power of 500 is outside the allowed range:

=5^500 // returns #NUM!

Example #2 - Impossible calculation

The #NUM! error also can appear when a calculation can't be performed. For example, the screen below shows how to use the SQRT function to calculate the square root of a number. The formula in C3, copied down, is:

=SQRT(B3)

#NUM! error example - impossible calculation

In cell C5, the formula returns #NUM, since the calculation can't be performed. If you need to get the square root of a negative value (treating the value as positive) you can wrap the number in the ABS function like this:

=SQRT(ABS(B3))

#NUM! error example - impossible calculation - FIXED

You could also use the IFERROR function to trap the error and return and empty result ("") or a custom message.

Example #3 - incorrect function argument

Sometimes you'll see the #NUM! error if you supply an invalid input to a function argument. For example, the DATEDIF function returns the difference between two dates in various units. It takes three arguments like this:

=DATEDIF (start_date, end_date, unit)

As long as inputs are valid, DATEDIF returns the time between dates in the unit specified. However, if start date is greater than end date, DATEDIF returns the #NUM error. In the scree below, you can see that the formula works fine until row 5, where the start date is greater than the end date. In D5, the formula returns #NUM.

#NUM! error example - wrong function argument

Notice this is a bit different from the #VALUE! error, which typically occurs when an input value is not the right type. To fix the error shown above, just reverse the dates on row 5.

Example #4 - iteration formula can't find result

Some Excel functions like IRR, RATE, and XIRR, rely on iteration to find a result. For performance reasons, Excel limits the number of iterations allowed. If no result is found before this limit is reached, the formula returns #NUM error. Iteration behavior can be adjusted at Options > Formulas > Calculation options.