While working on Excel, you must have faced multiple errors. Excel NUM error is one of those errors that appear when your formula in the worksheet does not calculate it. If you need to calculate square roots of some numbers and in the list of numbers there comes a negative value. It eventually ends in a #NUM error because we cannot find the square root of a negative number.
Having a negative value in the list is not the only reason for this error. There could be more than one reason behind the occurrence of this error. Each reason needs a different solution to the error and it will be a quick procedure for sure.
Basically, the calculation does not perform if there is an Excel NUM error.
Why Does NUM Error Occur?
Generally, there are multiple reasons behind this error, however, three reasons are common.
Reason 1: Faulty Numbers
One of the major reasons why this error appears is whether the number you have entered is too small or too big. If you are familiar with Excel, you must have an idea about the limitations on the largest and smallest numbers you can use.
If your added value is out of this range, you will surely see the #NUM error. Do you know the smallest value in Excel? The smallest value in Excel is -1*10^308. It simply means that -1 with 308 zeros after it. And the largest value in Excel is 1*10^308. It simply means a 1 with 308 zeros after it.
If the formula is =1000^500 it means that 1000 is multiplied by itself 500 times and it will end up as a larger number for Excel. And it ultimately results in the #NUM error.
Reason 2: Impossible Calculation in Excel
If you are trying to perform an impossible calculation in Excel, you will have to face an Excel NUM error. For instance, if you are using the SQRT function for computing the square root of a negative number, it will give you an error.
The #NUM error will occur if you want to find the logarithm of a negative value.
Reason 3: Iteration Formula Unable to Find Valid Result
With an iterative formula, you can run the calculation over and over again. Using the IRR function, it computes the internal rate of return for a number of cash flows. IRR formula calculates regularly to have an outcome within 0.00001%. If IRR is unable to calculate the result right after 20 shots, it will eventually end up in #NUM error.
How do You Fix the #NUM Error in Excel?
Fix 1: Impossible Calculations
If your calculation is impossible, you will have to face an Excel NUM error. The square root or positive numbers’ log has results, however, keep in mind that calculating the square root or log of the negative numbers is not possible.
Below you will see the procedure of the square root of -3.
As you already know, if you are having a square root of the negative number, it will end up in the NUM error. Talking about this case, you can put right this error with the absolute value of the number. With the ABS function, the absolute value of a number will return.
With this feature, all the negative numbers will replace with positive values and all the positive values will remain as it is.
In column A, we added the ABS function in the formula that consists of absolute value. That’s why Excel is now able to compute the number and return a valid answer. Did you get that point?
Fix 2: Iteration Formula is Unable to Compute Valid Result
Calculating the formula as IRR will eventually end up in the appearance of #NUM error. Here are some easy steps to follow that can simply change how many times Excel iterates formulas.
- Go to MS Excel and open File > Options.
- Hit the formula button and check the Enable iterative calculation box given under Calculation options.
- In the maximum iteration box, you have to add the larger value. Remember that Excel will calculate a worksheet as much as the iteration value is high.
- In the maximum change box, now you need to add the amount of change that is suitable for computing outcome. Try keeping the value as small as possible.
Fix 3: Fixing the Error with Too Large or Too Small Value
Well, no one can fix this issue in real, especially when the number value is larger or smaller than the preset value for Excel. You must have to change the input value so that the return value appears under the range.
Let’s have it clear with an example:
=100^500
Fix 4: IRR Function not Working
When you are using the IRR function, it might be possible that you are facing a NUM error in Excel. It could be of two reasons:
1. Formula is unable to find an outcome.
2. Cash flow does not have any negative or positive value.
Here we have explained it with an example that proves NUM error rather than giving a value.
It is just because the available cash flows are in the range that is positive. At least one negative value is needed in IRR functioning. You can simply have a look at the samples given below.
You can see that positive values offer income payments and negative values that show cash outflow gives primary payments. Doing this will ultimately give a perfect result.
Do you know that the IRR function can also come up with a #NUM error?
If the IRR function iterates and becomes unable to get some correct results after many attempts, you will see an Excel NUM error. To get rid of this thing, increasing the maximum value of iterations will help you.
Fix 5: Incorrect Function Argument is Entered
You definitely get an idea about the discussion we are going to do under this heading. You just have to enter the correct arguments. Let’s have a look at how it will work:
Suppose you have two columns having dates and the purpose is to calculate the difference between those dates. Here we will use another function used in Excel, which is DATEDIF. It might be possible that Excel does not display the arguments needed to add that’s why it could be a possible reason.
When you use the formula, you may switch the starting dates as ending dates and vice versa. So, ultimately the formula is not used in the right way.
=DATEDIF (B2, A2, “d”)
That way you will enter the arguments correctly in the formula and the Excel NUM error will not appear again.
=DATEDIF(A2, B2, “d”)
Final Thoughts
That’s it. Now you have more understanding about why NUM error appears and how you can fix this error. Best of Luck!
