Free Practice Workbook#NUM error in Excel occurs when values in formulas are invalid. It is mostly shown in cases where there is a problem with numbers i.e. the data type entered is not supported in the argument of the formula. Once you adjust the number input, the calculation can be performed and the error disappears! In the article, you will be shown the different reasons for #NUM error in Excel and how to fix it.
Key Takeaways
- Understand the Cause of the #NUM Error: The #NUM error occurs when Excel encounters invalid numerical operations, such as a calculation resulting in a value too large or too small, or an invalid argument in formulas like IRR, SQRT, or LOG.
- Check Input Values for Validity: Ensure all numerical inputs are within acceptable ranges. For example, avoid using negative numbers for operations like square roots or logarithms, which can trigger the error.
- Adjust Formula Iteration Settings: When working with iterative calculations (e.g., IRR or RATE), modify the maximum number of iterations or the precision tolerance in Excel’s Options under the Formulas tab to achieve a valid result.
- Use Error-Handling Functions: Implement error-handling formulas like
IFERRORorIFNAto display custom messages or alternative results when the #NUM error occurs, improving the readability and usability of your spreadsheet. - Review Complex Formulas: In complex calculations, isolate parts of the formula to identify the source of the error. Breaking down the formula into smaller, testable components can help pinpoint and resolve the issue efficiently.
Table of Contents
What does #NUM mean in Excel?
It basically means that the calculation cannot be performed due to limitations or errors.
This can be due to the fact that the number is too big or small. It also includes impossible calculations like calculating the square root of a negative number, or if the input for a function is invalid, or if an iterating formula like IRR cannot find the result.
How to fix #NUM error in Excel?
Let’s look into more detail and understand the causes and solutions to the #NUM! Excel.
Example 1 – Number size
Excel has a limitation to the size of the number that it can display. If you enter a number is too large or too small,you will get Excel #NUM Error.
As you can see, in cell C7 we are trying to multiply 5 by itself 500 times. The result will be a really large number that Excel is unable to display. Hence. it returns NUM Excel.
In cell C10, we are trying to divide 1 by 10^750. Since this will be a very small number Excel cannot perform calculations and hence returns #NUM error.
Excel is not able to show these values because of its inherent limitations. As shown, other smaller values are calculated and shown in C8 and C9.
Example 2 – Impossible calculations
Excel returns the #NUM! Error when it is faced with a calculation that is impossible to do.
As you can see, the square root or log of the positive numbers has results but the square root or log of the negative numbers is impossible to calculate.
In this example, we are trying to find the square root of -3.
Since we are trying to calculate the square root of a negative number, we will get #NUM error in Excel. In this particular case, you can rectify this issue by taking the absolute value of the number.
ABS function returns the absolute value of a number. It converts negative numbers to positive numbers and keeps the positive numbers unaffected.
Including the ABS function into the formula uses the absolute value of the number in column A, so now Excel can calculate the number and return a valid answer.
Example 3 – IRR function not working
Excel might show the #NUM! error when using IRR function because of either one of the two reasons:
- Cash flow does not contain at least one negative and one positive value.
- Formula can’t find a result.
In this example, Excel results a #NUM error instead of proving a value.
This is because the cash flows provided in the range (B7:B15) are positive and IRR requires at least one negative value as the initial cost of business.
Initial payments are provided as negative value indicating outflow of cash and income payments are represented by positive values. Once you have incorporated this, the function will provide the correct result!
IRR function can also display #NUM! error when the function iterates and can’t find a result after several tries.
To overcome this, you can increase the maximum number of iterations that Excel performs to get a result.
To increase the number of iterations:
STEP 1: Go to File > Options > Formulas to arrive at the window below
STEP 2: Under the Calculation options group, check the Enable iterative calculation box.
STEP 3: In the Maximum Iterations box, type the number of times you want Excel to recalculate.
The higher the number of iterations, the more time Excel needs to calculate a worksheet. Increasing this can help give a result to your calculation.
STEP 4: In the Maximum Change box, enter the amount of deviation that you are willing to accept between the calculation results.
The smaller the number, the more accurate the result. Increasing this can help give an approximate answer to your calculations.
Frequently Asked Questions
What causes the #NUM error in Excel?
The #NUM error occurs when Excel encounters invalid numerical operations. Common causes include entering numbers outside the allowable range, invalid inputs in formulas (e.g., negative numbers in SQRT or LOG), or iterative calculations like IRR or RATE failing to converge to a solution.
How can I fix the #NUM error in financial formulas like IRR or RATE?
To fix the error in financial formulas, adjust the initial guess for the calculation or modify Excel’s iteration settings. Go to File > Options > Formulas, and increase the maximum iterations or reduce the precision to help Excel find a solution.
How do I avoid the #NUM error when working with square roots or logarithms?
Ensure that inputs for SQRT, LOG, or similar formulas are valid. For example, only use non-negative numbers for SQRT and positive numbers for LOG. You can also use the IF function to check inputs before performing the calculation.
Can I handle #NUM errors automatically in Excel?
Yes, you can use the IFERROR function to handle #NUM errors. For example, =IFERROR(SQRT(A1), "Invalid input") will display “Invalid input” instead of the #NUM error if the value in A1 is negative.
What should I do if a formula returns a #NUM error due to extremely large or small numbers?
Check the calculation to ensure the values are within Excel’s limits (approximately ±10^308). If needed, scale the numbers down or use alternative calculations to keep the values manageable. This will prevent Excel from returning a #NUM error due to exceeding its numerical limits.
Conclusion
After this exercise, we hope that you now understand why you were experiencing the #NUM error in Excel and that you can now easily rectify and overcome this error and continue with smooth functioning in Excel!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
