When Excel isn’t sure what to do with your data or how to process a formula, it’s going to give you an error message. One of the common types of error messages that the program returns is the #NUM error.
The #NUM error generally happens when Excel has a function or formula that contains invalid numeric data. Here are some of the most common reasons that Excel returns this error and how you can fix it. We’ll also show you how to locate the #NUM error in your Excel spreadsheet.
If you want immediate and one-on-one help, you can bypass this tutorial and simply open a live chat window. Excelchat has Excel experts standing by that can help you resolve any issue related to Excel 24/7.
#NUM Error – Number is Too Big or Too Small
If the number you’re using in Excel is too big or too small, you’re going to get a #NUM error. The largest number you can use in Excel is 1*10 ^308, which is equal to 1 followed by 308 zeros, and the smallest number is -1*10 ^308, or -1 followed by 308 zeros.
For example, if you have a formula that multiplies 1000 by itself 500 times, the result is going to be a number that is larger than Excel can handle, so you’ll get a #NUM error (illustration below).
#NUM Error – Impossible Calculation
You’re also going to get the #NUM error if your calculation in Excel isn’t possible. For example, it’s not possible to find the square root or the logarithm of a negative number. If you ask Excel to do either of these using the SQRT or LOG functions, you’ll get a #NUM error.
If you need to get the square root of a negative number, you can accomplish this by first wrapping that number in the absolute value function (ABS). This will resolve the #NUM error and provide you with the correct answer, assuming your number is positive.
#NUM Error – Incorrect Function Argument
You might also find that Excel returns the #NUM error if you fail to provide the correct input to a function argument. For example, the DATEDIF function has this syntax:
=DATEDIF (start_date, end_date, unit)
The function delivers the difference between two dates in the requested units. As long as you provide valid inputs, this will work. But, what if the start date is after the end date? If this happens, you’ll get a #NUM error (see illustration). You can fix this simply be reversing the dates, provided that was your error.
#NUM Error – No Result Iteration Formula
Some Excel functions such as RATE, IRR, and XIRR use iteration to determine their result. To ensure that the program is able to perform properly — Excel limits the number of iterations permitted. If it doesn’t find a result before reaching this limit, it will return the #NUM error.
You can adjust iteration behavior in Excel in Options > Formulas > Calculation options.
Make sure that you check the Enable iterative calculation box. You can also adjust the Maximum iterations and Maximum Change numbers. The first is the number of times you’re directing Excel to recalculate, which increases the time to complete an operation. The second is the change you’ll accept between calculations results, with smaller numbers giving more accurate results.
Locating the #NUM Error in Excel
If you have a small spreadsheet, finding errors is simple enough. However, this can be a challenge when you have a large worksheet that contains a lot of data and formulas.
There are two ways to check for errors in your Excel spreadsheet. The first is with the Error Checking tool, which you can find in the Formulas tab under the Formula Auditing section. Just click on this and it will run through your data and look for errors, giving some options to help you fix them.
You can find errors in Excel using the “Find” function. This is located on the Home tab under the Editing section, in the Find & Select button. Choose Find, type in #NUM or NUM and then select Find All or Find Next. You can then view all of the instances of this error in your spreadsheet.
Fixing the #NUM Error in Excel
There is no simple fix for the #NUM error in Excel. While you could use Excel’s Find and Replace function to “replace” every instance of the #NUM error with something else, you’re probably going to compromise the integrity of your worksheet by doing this.
The better option is to locate each instance of the error, determine the cause, and fix them individually. These tips can help you fix these errors. You can also click on the small yellow box to the left of the #NUM error message on your page. This gives you several helpful options, such as Show Calculation Steps, Edit in Formula Bar, Ignore Error, and get Help on this Error.
Hopefully, this piece helped you better understand how the #NUM error occurs so that you can avoid it, locate it, and fix it. If you weren’t able to resolve your issue or would like some other help related to Excel, let us know.
Excelchat can provide personalized instruction on Excel or help you troubleshoot an error that is holding you back. Just open a live chat window to get started immediately. Your first session is always free.
Leave a Comment