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:
- Value : Required value. Arguments to check for errors.
- Value_if_error : Value to return if the formula evaluates to an error. Example 0.
Good luck!
You should read it
- How to correct a #NA error in Excel
- How to correct a #REF! Error in Excel
- How to correct a #NAME error in Excel
- How to fix the SUM function doesn't add up in Excel
- What is ### error in Excel? how to fix ### error in Excel
- Instructions on how to fix reverse dates in Excel
- How to use the FIND function in Excel?
- how to fix error cannot copy data in Excel files
- How to fix the date #VALUE error in Excel
- How to fix date format errors in Excel
- How to correct when Excel automatically rounds numbers
- How to fix Vietnamese error when opening CSV file in Excel
Maybe you are interested
Quickly fix Unmountable Boot Volume error on Windows 10/11
How to Fix Clipboard History Error in Windows 11 Latest Update
How to fix mouse not working error in GTA Vice City on Windows 10
5 Ways to Fix 'Can't Reach This Page' Error on Microsoft Edge
Bluetooth laptop error, cause and how to fix
How to fix Cannot renew IP address error on Windows