In this tutorial, we will know what is #NUM error in Excel at the same time why and when do we get this error in our calculations.
Error is a common and necessary part of Excel formulas and functions, but fixing those errors is what distinguishes you as an Excel expert.
Definitely, as a beginner finding those errors and restoring them to work properly again is a little bit hard and tricky. However, every error is the result of the user’s error. The reason why it is significant to understand why this occurs.
What does #NUM error mean in Excel
#NUM! error basically means that calculations cannot be executed due to limitations or errors.
This could be the fact that the number is too small or big. Additionally, it perhaps includes impossible calculations for instance finding the square root of a negative number, or maybe the function is invalid, or might be the iterating formula such as IRR can’t find the result.
How to fix #NUM Error in Excel
Here are some examples of ways to fix #NUM! errors. Along with common reasons why we encounter this #NUM! error.
Impossible Calculation in Excel
One of the causes of having #NUM! error is an Impossible calculation. If you are trying to do this definitely you will encounter this error.
For instance, if you are trying to calculate the square root using SQRT function of some negative number, apparently this cannot be calculated.
To fix the square root of a negative number this you can consider the following ways:
- Alternatively, to get rid of this error we need to get the absolute value of the number. Using the ABS function will return this to its absolute value.
- Using this function will replace all negative numbers with positive while positive number will remain as it is.
- In column G, we added the ABS function to the formula. That’s why Excel is now able to compute the number and return a valid answer. Did you get that point?
Note: To avoid this kind of error analyze first the limitations of the calculation, if it's impossible figure out alternative ways.
Incorrect Function Argument Entered
As the header itself, you can determine that Incorrect function argument entered is the cause of #NUM! error.
This definitely occurs when supplied function argument is invalid.
For example, the DATEDIF function returns the difference between two dates in various units. It takes three arguments like this:
=DATEDIF (start_date, end_date, unit)
As long as inputs are valid, DATEDIF returns the time between dates in the unit specified. However, if the start date is greater than the end date, DATEDIF returns the #NUM error.
In the given image above, you can see that the formula works fine for rows whose start date is greater than the end date. And on rows whose start date is less than to Endate return #NUM error.
In order to fix this, all you have to do is reverse the dates where the start date is less than to end date.
Note: This is way different in #VALUE! error, wherein it occurs typically when the entered value is not the right type.
Incorrect Numbers
One of the main reasons for this error is that the number you entered is either too small or too large. If you’re familiar with Excel, you’re probably aware of the restrictions on the largest and smallest numbers you can use.
Definitely, if your value added is out of this range, you will encounter the #num error.
Ddo 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.
Conclusion
That’s all we need to know about #NUM error in Excel. So in this tutorial, we covered the possible reason along with possible ways to get rid of this error in our worksheet.
Now that you know what cause of this error it is easy for you now to troubleshoot your calculations when you faced this kind of error.
I hope this tutorial is useful in doing your task. Thank you for reading 🙂