How to Fix the #NUM Error in Excel?

The #NUM error in Excel practically means there’s something wrong with the numeric values in your Excel formula. This error, however, can mean different things depending on the context of the Excel function used.

In this article, I’ll show you all the possible reasons why the #NUM error appears and what you can do to fix it.

What causes the #NUM error in Excel?

The #NUM error gives us a bit of a clue of what’s causing the error – the numeric values.

The error could either be caused by:

• one of the values added as arguments in the function; or
• the resulting values after the calculation

You could either:

• correct the arguments added (or the contents of the cells referred to); or
• edit the Excel formula so that it hides the error

As mentioned, the #NUM error can mean different things in different Excel functions:

• The #NUM error in DATEDIF may mean that the end date is lesser than (or earlier than) the start date. It should be the other way around.

• The #NUM error in IRR, RATE, XIRR and other iterative functions may mean that Excel can’t find the result within the set Maximum Iterations and Change.

• The #NUM error in XNPV may mean that:
  • the values do not contain at least one positive and one negative value;
  • the dates come before the starting date
  • values and dates have a different total number of values.

• The #NUM error in PERCENTILE may mean that:
  • the array is empty (or pointing to cells that have non-numeric values)
  • “k” is less than 0 or greater than 1 (note that “k” should be between 0 and 1) 

• The #NUM error in other functions may mean that the arguments provided result in:
  • a number that is either way too small or way too large to display
  • an invalid calculation (cannot be performed)

Fix the #NUM Error

To fix the #NUM error in Excel, the first thing that you should do is identify the probable cause of the error.

To do this:

1. Select the cell that contains the #NUM error.

2. Go to “Formulas” >> “Formula Auditing” section >> “Evaluate Formula”

3. The Evaluate Formula menu will appear. Click on the Evaluate button. Notice that it processes one section of the formula at a time.

4. Click on the Evaluate button again until you see the #NUM error. You will then have an idea of where the error started.

Once you’ve identified the probable cause, the next step is to apply the appropriate solution.

1. DATEDIF Function

When working on the DATEDIF function, ensure that the start date is earlier than the end date. Otherwise, you’ll have the #NUM error (as shown below).

In the example above, notice that the DATEDIF formula results in a #NUM error because the start date is greater (or later) than the end date.

You have two options to fix this:

• Update the Start and End Dates so that the Start Date is earlier than the End Date.

• Edit the formula so that it displays something else. Use this option whenever you require your user to input the Start and End Dates. You could have a text like “INVALID INPUT” appear whenever an error occurs. You could use the IFERROR() function for this (same as the screenshot below).

2. IRR, RATE, XIRR, and other iterative functions

The #NUM error in IRR, RATE, XIRR and other iterative functions may mean that Excel can’t find the result within the set Maximum Iterations and Change.

As you may already know, iterative functions run calculations over and over using previous results.

Depending on the numbers you are working on, you may need to set the limit for the number of times that Excel runs the iterative functions so that you’ll achieve your desired result. Because if not, you may experience the #NUM error.

To do this:

1. Go to File >> Options

2. In the Excel Options menu, select Formulas. From there, adjust the Maximum Iterations and Maximum Change. 

• In the Maximum Iterations textbox, enter the number of times you want the calculation to recalculate.

• In the Maximum Change textbox, enter the amount of change you’ll accept between the calculation results.

3. See if your adjustments fixed the error. If not, adjust the iteration settings again.  

3. XNPV Function

The #NUM error in XNPV may mean that:

• the values do not contain at least one positive and one negative value;
• the dates come before the starting date; or
• values and dates have a different total number of values.

As you may already know, the XNPV function returns the net present value for a schedule of cash flows. The series of payments must at least have one positive value and one negative value (for the cost).

When working on this Excel function, ensure that the dates come after the starting date.

You could either:

• Adjust the start date so that it comes before the dates in the selected date range; or
• Adjust the date range so that only those that come after the start date are covered

See to it that the values and dates have the same number of values. If not, you’ll have the #NUM error.

4. PERCENTILE Function

The PERCENTILE function, as you may already know, returns the “k” percentile of values in the specified range.

The #NUM error may appear in this function if:

• The array is empty (or pointing to cells that have non-numeric values)
• “k” is less than 0 or greater than 1 (note that “k” should be between 0 and 1)

To fix this:

1. Ensure that the range selected for the array argument is not left blank and contains numeric values.

2. “k” should be a number between 0 and 1. It can’t be negative (less than 0) nor greater than 1.

5. Other Excel functions

For other Excel functions, the #NUM error may either mean that:

• a number that is either way too small or way too large to display
• an invalid calculation (cannot be performed)

The screenshot below shows some of the reasons why the #NUM error appears in an exponential formula (^):

As you can see, the #NUM error appears when:

• both the base and exponents are left blank or are equal to 0; or 
• the arguments result in an infinite number.

To fix these errors, you may need to change the arguments so that the calculation becomes valid. You may want to try the formula on a calculator and see if computation is possible.

The #NUM errorin the SQRT() function may appear if you try to get the square root of a negative number.

To fix this, you could update the formula to get the absolute value of the number (disregarding the negative sign, if any) before the square root.

You can use this formula: =SQRT(ABS([range]))

Note This:

The #NUM error can be a bit tricky to fix, but you could follow these four simple steps to make it bearable:

1. Use the Evaluate Formula option to find where the error started in your formula. 

2. Once identified, check if the arguments for that Excel function are valid – see if the ranges referred to all contain numbers (if numbers are required), etc.

3. If the arguments are all valid, manually perform the calculation. See if it is possible to do the computation with the numbers provided. You may want to use a calculator to help you with this.

4. If an error is inevitable, you could replace it with a text or a number (e.g., 0) using the IFERROR() function.

Conclusion

The #NUM error essentially means there’s a problem with the numeric values – either with the arguments added in the formula or the resulting values after the calculation.

To fix this, identify the probable cause of the error using Excel’s Evaluate Formula option. Once that is figured out, apply the solution fitting for the cause of the error.