Example #1 - Number too big or small
Excel has limits on the smallest and largest numbers you can use. If you try to work with numbers outside this range, you will receive the #NUM error. For example, raising 5 to the power of 500 is outside the allowed range:
Example #2 - Impossible calculation
The #NUM! error also can appear when a calculation can’t be performed. For example, the screen below shows how to use the SQRT function to calculate the square root of a number. The formula in C3, copied down, is:
In cell C5, the formula returns #NUM, since the calculation can’t be performed. If you need to get the square root of a negative value (treating the value as positive) you can wrap the number in the ABS function like this:
You could also use the IFERROR function to trap the error and return and empty result ("") or a custom message.
Example #3 - incorrect function argument
Sometimes you’ll see the #NUM! error if you supply an invalid input to a function argument. For example, the DATEDIF function returns the difference between two dates in various units. It takes three arguments like this: As long as inputs are valid, DATEDIF returns the time between dates in the unit specified. However, if start date is greater than end date, DATEDIF returns the #NUM error. In the scree below, you can see that the formula works fine until row 5, where the start date is greater than the end date. In D5, the formula returns #NUM.
Notice this is a bit different from the #VALUE! error, which typically occurs when an input value is not the right type. To fix the error shown above, just reverse the dates on row 5.
Example #4 - iteration formula can’t find result
Some Excel functions like IRR, RATE, and XIRR, rely on iteration to find a result. For performance reasons, Excel limits the number of iterations allowed. If no result is found before this limit is reached, the formula returns #NUM error. Iteration behavior can be adjusted at Options > Formulas > Calculation options.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
