How to Fix #NUM! Errors in Microsoft Excel

Make your spreadsheets more reliable

Understanding and resolving #NUM errors in Excel is more than a troubleshooting task; it’s an essential skill for anyone working with Excel. Whether navigating financial data, conducting scientific research, or managing inventory, theseformula errorscan surface and compromise your analysis. In this article, we’ll guide you through different ways of fixing the #NUM error.

Also, make sure to check out our guides onhow to fix #N/A Errors in Excel formulasand the commonly encounteredsharing violation error.

What Is the #NUM Error and Why Does It Occur?

The Excel #NUM error message is one of the most common errors. It’ll get triggered for the following reasons:

Fix the #NUM Error Caused By the Incorrect Function Argument

The most common reason behind the #NUM error is the invalid argument or incorrect data types. If you suspect this is causing the #NUM error in your function, check the data types and your formula syntax for any mistakes.

For example, if you’re using the DATE function, Excel expects you to use only numbers between 1 and 9999 for the year argument. If you provide it with a year value that’s outside this range, it’ll result in the #NUM error.

Similarly, if you’re using the DATEDIF function, the provided end date must be greater than the start date. You can also have equal date entries. But if it’s the opposite, the result will be the #NUM error.

Let’s see it in an example:

=DATEDIF(A2,B2,”d”) calculates the difference in the number of days between two dates (in cells A2 and B2). The result would be numerical if the date in cell A2 is less than the date in cell B2. If this is not the case, as in our example below, the result will be the #NUM error.

Fix the #NUM Error Caused By Too Large or Too Small Numbers

If your formula has an argument that exceeds the Excel’s number limit, it’ll result in the #NUM error. Yes, Microsoft Excel has a limit on the size of numbers it can calculate. To tackle this, you’ll have to adjust the input value so that the result falls in the allowed range.

If you must work with such large numbers, consider breaking the calculation into smaller parts. This will allow you to use multiple cells to achieve the final result.

Here are the calculation limits in Microsoft Excel:

If the result of the formula you’re using is outside of these scopes, the result you’ll receive will be the #NUM error.

Let’s see it as an example.

Note: in newMicrosoft Excel versionsonly large numbers will result in the #NUM error. The numbers that are too small will result in 0 (general format), or 0.00E+00 (scientific format).

If you’re unfamiliar with the scientific format, remember that, for example, the ”E-20” part translates to “times 10 to the power of -20”.

Fix the #NUM Error Caused By Impossible Calculations

Another reason for getting the #NUM error is if Excel deems the calculation impossible. In that case, you’ll have to identify the function or the part of the function that’s causing the problem and adjust your formula or inputs accordingly.

The most typical example of an impossible calculation is attempting to find the square root of a negative numeric value. Let’s see it in the example with the SQRT function:

If you use =SQRT(25) the result will be 5.

If you use =SQRT(-25) the result will be #NUM.

You can fix this issue if you apply the ABS function and you’ll get the absolute value of the number.

=SQRT(ABS(-25))

Or

=SQERT(ABS(cell_reference))

Similarly, Excel doesn’t support complex numbers so any calculation you perform that’ll result in a complex number will result in a #NUM error. In that case, the calculation is impossible to achieve within the limitations of Excel.

An example of this would be trying to raise a negative number to a non-integer power.

Fix #NUM Error When Iteration Formula Cannot Converge

You might be getting the #NUM error because the formula you’re using cannot find the result. If this is the case, you’ll have to modify the input values and help the formula perform the calculation.

Iteration is an Excel feature that allows formulas to repeatedly calculate until the proper conditions are met. That means the formula will try to find the result through trial and error. There are several Excel functions that use the iteration feature: IRR, XIRR, or RATE. An iteration formula that can’t find the valid result within the given parameters will return the #NUM error.

For example:

Help your formula by providing an initial guess:

You may need to adjust the iteration settings in Excel in order to help your formula to converge. Here’s how to do it:

Fix the #NUM Error in the Excel IRR Function

If there’s a non-convergence issue, your IRR formula will fail to find the solution. The result would be the #NUM error. To deal with this, you’ll have to adjust Excel’s iteration settings and provide the initial guess.

Like with other iteration functions, the IRR function can result in a #NUM error because the formula can’t find the result after a certain number of iterations. You can easily resolve this issue if you increase the number of iterations and provide the initial guess. Look at the previous section for examples.

If you’re dealing with inconsistent signs, like in our example below, make sure to input all initial outgoing cash flows as negative numbers. The function assumes that there are both positive and negative cash flows.

For example, if your Investment is set to $10,000, the IRR function will result in a #NUM error.

If you change the Investment to -$10,000, as a negative input like so:

The IRR function will work.

Note that modern versions of Microsoft Excel will change your negative input into brackets (our -$10,000 was changed into ($10,000)), so don’t worry if you see this happening. The formula will still work.

By following the steps and best practices outlined in this article, you’re now equipped to tackle those pesky #NUM! issues head-on, ensuring that your Excel spreadsheets remain reliable and error-free. Happy spreadsheet crafting!

Nicolae is a Jack of all trades technology writer with a focus on hardware, programming languages, and AI image-processing software. Over the last five years, he has ghostwritten numerous tech how-to guides and books on a variety of topics ranging from Linux to C# programming and game development. Nicolae loves everything that has to do with technology and his goal is to share his knowledge and experience with others.Read Nicolae’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved