How to remove #NUM error in Excel
To remove the #NUM error from Microsoft Excel, follow the methods below depending on the scenario:
1] Number is too big or small
The #Num error will occur in your Excel spreadsheet when the formula results are too small or large. Excel has a limit of the largest and smallest numbers that users can use. See photo above.
2] Impossible calculations
Excel will return a #NUM error if the calculation cannot be performed. For example, in the photo above, Excel returns a #NUM error in cell B4 due to the number in cell A4, which is impossible to calculate. See photo above.
If you want to get the result of a negative value, you have to use the ABS function. The ABS function returns the absolute value of a number; it converts negative numbers to positive numbers. Place the ABS function into the SQRT function, for example, =SQRT(ABS(A4)); this will convert the negative number to a positive.Please see photo above.
3] Iteration formula can’t find a result
Excel will show the #NUM error if a formula uses a function that iterates, such as IRR or RATE, and cannot find a result. Follow the steps below to change the number of times Excel iterates formulas: Select File. On the backstage view, click Options. An Excel Options dialog box will open.
Click Formulas on the left pane. Under the Calculations options section, check the Enable iterative calculation check box In the Maximum iterations box, type the number of times you want Excel to recalculate. The higher the number of iterations, the more time Excel needs to calculate the worksheet. In the Maximum Change box, type the amount of variation you will accept between calculation results. The smaller the number, the more accurate the result, and the more time Excel needs to calculate a worksheet. Then click OK. Read: How to add, insert and use Notes in Excel
How do you change NUM to 0 in Excel?
To change the #NUM error to zero, follow the steps below: We hope this tutorial helps you understand how to remove the #NUM error in Excel; if you have questions about the tutorial, let us know in the comments.
