The #NUM error happens in your Excel spreadsheet when a operate or system incorporates invalid numeric values. This normally happens whenever you enter a numeric worth utilizing an information kind or a quantity format that’s not supported in the argument part of the system.
How to remove #NUM error in Excel
To remove the #NUM error from Microsoft Excel, observe the strategies beneath relying on the situation:
- Quantity is simply too large or small
- Not possible calculations
- Iteration system can’t discover a end result
1] Quantity is simply too large or small
The #Num error will happen in your Excel spreadsheet when the system outcomes are too small or massive. Excel has a restrict of the most important and smallest numbers that customers can use. See picture above.
2] Not possible calculations
Excel will return a #NUM error if the calculation can’t be carried out. For instance, in the picture above, Excel returns a #NUM error in cell B4 due to the quantity in cell A4, which is inconceivable to calculate. See picture above.
In order for you to get the results of a unfavorable worth, you’ve to use the ABS operate. The ABS operate returns absolutely the worth of a quantity; it converts unfavorable numbers to constructive numbers. Place the ABS operate into the SQRT operate, for instance, =SQRT(ABS(A4)); this can convert the unfavorable quantity to a constructive.Please see picture above.
3] Iteration system can’t discover a end result
Excel will present the #NUM error if a system makes use of a operate that iterates, comparable to IRR or RATE, and can’t discover a end result. Observe the steps beneath to change the variety of occasions Excel iterates formulation:
Choose File.
On the backstage view, click on Choices.
An Excel Choices dialog field will open.
Click on Formulation on the left pane.
Below the Calculations choices part, examine the Allow iterative calculation examine field
Within the Most iterations field, kind the variety of occasions you need Excel to recalculate. The upper the variety of iterations, the extra time Excel wants to calculate the worksheet.
Within the Most Change field, kind the quantity of variation you’ll settle for between calculation outcomes. The smaller the quantity, the extra correct the end result, and the extra time Excel wants to calculate a worksheet.
Then click on OK.
Learn: How to add, insert and use Notes in Excel
How do you modify NUM to 0 in Excel?
To vary the #NUM error to zero, observe the steps beneath:
- Choose the num error to zero.
- Click on the Discover and Choose button in the Modifying group.
- Choose Go to Particular from the drop-down record.
- A Go to Particular dialog field will seem.
- Choose Formulation and solely the Errors examine field must be checked.
- Then click on OK.
- All of the errors can be chosen in the chosen vary of cells.
- kind zero into the cells containing the errors and press the mixture keys Ctrl + Enter.
- The zero worth will substitute the error.
We hope this tutorial helps you perceive how to remove the #NUM error in Excel; when you have questions concerning the tutorial, tell us in the feedback.
