#NUM error in Excel - fixing number error in formulas

Invalid input arguments

Reason: Some Excel functions require specific input arguments. If you provide invalid arguments or incorrect data types, Excel may produce a #NUM error.

Solution: Make sure that all input arguments are valid and of the correct data type. Double-check your formula for syntax errors.

For instance, the DATE function in Excel expects a number between 1 and 9999 for the year argument. If the provided year value is outside this range, the #NUM error will occur.

Similarly, the DATEDIF function requires that the end date be always greater than the start date (or both dates are equal). If the opposite, the formula will produce a #NUM error.

For example, this formula will return the difference of 10 days:

=DATEDIF("1/1/2023", "1/11/2023", "d")

And this one will raise the #NUM error:

=DATEDIF("1/11/2023", "1/1/2023", "d")

Too large or too small numbers

Reason: Microsoft Excel has a limit on the size of numbers that it can calculate. If your formula produces a number that exceeds this limit, a #NUM error occurs.

Solution: If your formula produces a number outside the limit, adjust the input values to ensure that the result falls within the allowable range. Alternatively, you can consider breaking the calculation into smaller parts and using multiple cells to arrive at the final result.

The modern versions of Excel have the following calculation limits:

If you are not familiar with the scientific notation, I'll briefly explain what it means on an example of the smallest allowed positive number 2.2251E-308. The "E-308" part of the notation means "times 10 to the power of -308", so the number 2.2251 is multiplied by 10 raised to the power of -308 (2.2251x10^-308). This is a very small number that has 307 zeros in the decimal part before the first non-zero digit (which is 2).

As you see, the allowed input values in Excel are between -2.2251E-308 and 9.99999999999999E+307. In formulas, numbers between -1.7976931348623158E+308 and 1.7976931348623158E+308 are allowed. If the result of your formula or any of its arguments is outside the scope, you'll get the #NUM! error.

Please notice that in the current version of Excel 365, only large numbers produce a #NUM error. Small numbers that are outside the limit show up as 0 (General format) or 0.00E+00 (Scientific format). Looks like a new undocumented behavior:

Impossible operations

Reason: When Excel is unable to perform a calculation because it is deemed impossible, it returns the #NUM! error.

Solution: Identify the function or operation that is causing the error and adjust the inputs or modify the formula accordingly.

A typical example of an impossible calculation is attempting to find the square root of a negative number using the SQRT function. Since the square root of a negative number cannot be determined, the formula will result in a #NUM error. For example:

=SQRT(25) – returns 5

=SQRT(-25) – returns #NUM!

To fix the error, you can wrap a negative number in the ABS function to get the absolute value of a number:

=SQRT(ABS(-25))

Or

=SQRT(ABS(A2))

Where A2 is the cell containing a negative number.

Another case is the exponentiation of a negative number to a non-integer power. If you try to raise a negative number to a non-integer power, the result will be a complex number. As Excel does not support complex numbers, the formula will produce a #NUM error. In this case, the error indicates that the calculation is impossible to perform within Excel's constraints.

Iteration formula cannot converge

Reason: An iteration formula that cannot find a result causes the #NUM error in Excel.

Solution: Modify the input values to help the formula converge, or adjust the Excel iteration settings, or use a different formula altogether to avoid the error.

Iteration is a feature in Excel that allows formulas to repeatedly recalculate until a certain condition is met. In other words, a formula tries to achieve the result through trial and error. In Excel, there are a number of such functions including IRR, XIRR and RATE. When an iteration formula cannot find a valid result within the given constraints, it returns the #NUM error.

To fix this error, you may need to change the input values or decrease the tolerance level for convergence to ensure that the formula is able to find a valid result.

For example, if your RATE formula results in a #NUM error, you can assist it in finding a solution by providing an initial guess:

Additionally, you may need to adjust the iteration settings in Excel to help the formula converge. Here are the steps to follow:

1. Click on the File menu and select Options.
2. On the Formulas tab, under the Calculation options section, check the Enable iterative calculation option.
3. In the Maximum Iterations box, enter the number of times you want Excel to perform formula recalculations. A higher number of iterations increases the likelihood of finding a result, but also requires more time to calculate.
4. In the Maximum Change box, specify the amount of change between calculation results. A smaller number provides more accurate results but requires more time to calculate.

#NUM error in Excel IRR function

Reason: An IRR formula fails to find a solution due to non-convergence issues or when the cash flows have inconsistent signs.

Solution: Adjust Excel's iteration settings, provide an initial guess, and ensure the cash flows have appropriate signs.

The IRR function in Excel calculates the internal rate of return for a series of cash flows. However, if the cash flows do not result in a valid IRR, the function returns a #NUM! error.

As with any other iteration function, a #NUM error in IRR may occur because of the inability to find an outcome after a certain number of iterations. To resolve the issue, increase the maximum number of iterations or/and provide the initial guess for the return rate (see the previous section for more details).

Another reason why the IRR function may return a #NUM error is due to inconsistent signs in the cash flows. The function assumes that there are both positive and negative cash flows. If there is no change in sign for the cash flows, an IRR formula will result in the #NUM error. To fix this issue, ensure that all outgoing payments, including the initial investment, are entered as negative numbers.