How to correct a #NUM error in Excel

In this article, Dexterity Software will discuss the #NUM error and how to correct errors in Excel.

#NUM error and causes

The #NUM error is an error when a formula or function contains invalid numeric values.

Cause of the #NUM error:

- Some functions did not enter valid arguments in order to calculate results.

- The result of the calculation is too large (outside the range of -1x10307 and 1x 10307).

How to fix the #NUM error

Before fixing error # NUM, you must know which of the above causes is the solution to the problem.

Error #NUM! appears due to unreasonable argument

Example 1: SQRT function

You need to calculate the square root of an argument that is a negative value, the SQRT function will return the #NUM error value:

In this case, you just need to convert the argument to a positive number using the ABS function before performing the square root function SQRT .

Example 2: DATEDIF function

The structure of the DATEDIF function ( start_date, end_date, unit ), requires the end_date argument to be greater than the start_date argument , if the DATEDIF function fails #NUM, you should check if the argument end_date is smaller than start_date ?

Example 3: IRR function

The IRR function is also one of the most prone to #NUM errors, because of its efficiency, Excel limits the number of iterations. If no results are found before this limit is reached, the formula will return the #NUM error.

To adjust the repeat behavior, you can access File => select Options => Formulas => Calculation options => in the Maximum Iterations box , enter the number of times you want Excel to recalculate. The higher the number of iterations, the more times Excel needs to calculate a worksheet.

The #NUM error as a result of the calculation is too large

In some calculations, because the result of the calculation is outside the range outside -1x10307 and 1x 10307, ​​Excel returns the #NUM error value .

In the above case, you need to reduce the value of the calculation.

To fix the error # NUM , you need to know the cause from which to have the appropriate solution. To ignore the #NUM error , that is, the cells with the #NUM error result will return 0, you combine the IFERROR function with the formula = IFERROR (value, value_if_error) . Inside:

1. Value : Required value. Arguments to check for errors.
2. Value_if_error : Value to return if the formula evaluates to an error. Example 0.

Good luck!