The #NUM error occurs in your Excel spreadsheet when a function or formula contains invalid numeric values. This usually occurs when you enter a numeric value using a data type or a number format that is not supported in the argument section of the formula.
How to remove #NUM error in Excel
To remove the #NUM error from Microsoft Excel, follow the methods below depending on the scenario:
- Number is too big or small
- Impossible calculations
- Iteration formula can’t find a result
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:
- Select the num error to zero.
- Click the Find and Select button in the Editing group.
- Select Go to Special from the drop-down list.
- A Go to Special dialog box will appear.
- Select Formulas and only the Errors check box should be checked.
- Then click OK.
- All the errors will be selected in the selected range of cells.
- type zero into the cells containing the errors and press the combination keys Ctrl + Enter.
- The zero value will replace the error.
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.
