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
How to diagnose computer errors through beeps - Computer error reporting through beeps
Fix the error of not being able to hide phone numbers on Telegram
What is the error of the website automatically redirecting to another page on the phone?
How to insert videos into PowerPoint easily and without errors
How to fix Chrome tab auto-refresh error
Sound Booster error and how to fix it when using it