We are all visited by the unwelcome #NUM! error in our Excel worksheets from time to time.
What’s annoying about the #NUM! error in Excel is that the error message does not provide any details about the specific issue, so you will need to troubleshoot the problem to figure out what is causing the #NUM! error.
This tutorial lists the causes of the #NUM! error and shows how to fix it using examples.
What Causes of the #NUM! Error in Excel?
There are four main reasons why you may encounter the #NUM! error in your Excel worksheets:
- The formula you entered can’t perform the calculation; for example, find the square root of a negative number.
- The formula you entered results in a number too large or too small to be displayed in Excel. The smallest and the largest numbers that Excel can display are -1*10308 and 1*10308, respectively.
- You have entered a formula that uses a function that iterates, for example, RATE and IRR, and it can’t find a result.
- You have entered arguments incorrectly in a function. For example, If you are using the DATEDIF function and mistakenly enter an end date that is earlier than the start date.
In summary, the #NUM! error essentially means that there is a problem with the numeric values in either the result of the calculation of the formula or in the arguments passed to the formula.
Also read: #NAME? Error in Excel – How to Fix!
How to Fix the #NUM! Error in Excel?
The first step in fixing the #NUM! error is to establish its probable cause by using the steps below:
- Select the cell containing the #NUM! error as in the case below:
- Click the Formulas tab, and select the Evaluate Formula option in the Formula Auditing group.
Note: The Evaluate Formula option steps through a formula piece by piece, allowing us to debug the formula by evaluating each part individually.
- Click the Evaluate button on the Evaluate Formula dialog box that appears. The formula is processed one section at a time.
- Click the Evaluate button several times until the #NUM! error appears.
You get a good idea of what caused the error.
Once you have identified the cause of the error, you can easily fix it.
Example #1: Fix the #NUM! Error When a Formula Can’t Perform the Calculation
In the following dataset, the SQR function can’t calculate the square root of the negative value in cell A3 and returns a #NUM! error.
You can fix this error in two ways.
First, you can manually remove the negative sign from the value in cell A3 or use the ABS function to pass absolute values to the SQR function, as seen below:
Also read: How to Square a Number in Excel
Example #2: Fix the #NUM! Error When Output is Too Small or Too Large
In the following dataset, the output in cell C2 is too large to be displayed, and the output in cell C3 is too small to be displayed.
You can fix the #NUM! error in this situation by changing the formula to yield a result between -1*10308 and 1*10308.
Also read: #VALUE! Error in Excel – How to Fix!
Example #3: Fix the #NUM! Error Caused By Functions that Iterate
Functions that iterate can throw up the #NUM! error in two situations:
- The function can’t find the result after maxing out the specified iterations.
- The indicated cash flow does not have at least one negative and a positive value.
Fix the #NUM! Error Caused by Formula Not Finding Result After Maxing Out Iterations
We must change the number of times the function iterates to fix this error.
We use the steps below:
- Click the File tab to open the Backstage window.
- Click Options on the left sidebar of the Backstage window.
- Select the Formulas category on the left sidebar of the Excel Options dialog box that appears. Select the Enable iterative calculation option on the right, increase the value in the Maximum Iterations spinbox to the desired number and click OK. Remember that the higher the number of iterations, the longer the formula takes to calculate the result.
Fix the #NUM! Error Caused by Cash Flow Not Having At Least One Negative and Positive Value
The IRR function in the following dataset returns a #NUM! error because the cash flow values in range B2:B13 do not include at least one negative value.
The IRR function needs at least one negative value as the starting cost of the business.
To fix this error, include at least one negative value in the cash flow range, and the IRR function returns a valid outcome.
Also read: Excel Showing Formula Instead Of Result
Example #4: Fix the #NUM! Error Caused By Incorrect Entry of Arguments
Sometimes the incorrect entry of arguments in a function causes the #NUM! error.
Take, for example, the DATEDIF function. This is a legacy function retained in Excel for compatibility with Lotus 1-2-3.
When you enter this function, Excel IntelliSense does not assist you in entering the arguments the way it does for the other built-in functions.
This means it is easy to make mistakes when entering arguments.
For example, you may switch the start and the end date, as in the example below, resulting in #NUM! error:
To fix this error, all you need to do is to ensure that you enter the start date before the end date, and the function returns a valid result:
Also read: #REF! Error in Excel
Example #5: Suppress the #NUM! Error and Display an Informative Message
We can edit the formula to display an informative message instead of the #NUM error, to help the user enter valid values in the formula.
For example, you could display the text INCORRECT ENTRY instead of the #NUM! error.
We can use the IFERROR function for this purpose, as in the example below:
In this tutorial, we looked at the four causes of the #NUM! error in Excel.
The causes are attempting to perform an impossible calculation, the result of the formula being either too small or too large to be displayed by Excel, functions that iterate can’t get a result, and incorrect entry of arguments.
We looked at several techniques for fixing the #NUM! error.
They include using the ABS function to prevent negative numbers from being passed to the SQR function, increasing the number of iterations, entering the arguments correctly and suppressing the error, and displaying an informative message instead.
In general, the “NUM” error is a common and frustrating problem in Excel, and it can be difficult to fix if you don’t know what is causing the error.
I hope the steps I provided above will help you troubleshoot and fix the error.
Other Excel articles you may also like:
