How to Fix the #NUM! error

What is the Meaning of #NUM For Excel?

It is basically saying it means that the calculation is not able to be completed because of restrictions or mistakes.

This could result from the possibility it is because the number is either too large or too small. This also applies to unsolvable calculations such as the calculation of an equation for the square root in a negative value, or in the event that the input for a formula is not valid or if an iterative formula such as IRR is unable to find the answer.

Follow this thorough instruction for the #NUM Excel along with Download the Excel Workbook to get a feel for it and gain a better understanding of:



Why Does NUM Error Occur?

In general, there are a variety of causes for this error, however, three causes are typical.

Reason 1: Faulty Numbers

One of the main causes of this error is that the value you entered is either too small or too large. If you’re familiar with Excel and Excel, you should know the limitations of the biggest and smallest numbers you could utilize.

If the value you’ve added falls not within the range of this it is likely that you will encounter the #NUM error. Are you aware of the smallest number in Excel? The smallest number in Excel is -1*10308. This simply means that 1 with 308 zeros in it. The largest number in Excel is 1*10308. This is simply a 1. with 308 zeros following it.

When the formula reads 1000500, it means that 1000 has been multiplied by 500 and will result as a bigger number in Excel. It will ultimately result in an error code #NUM.

Reason 2: Impossible Calculation in Excel

If you’re trying to do an impossible calculation with Excel You will need to confront the Excel NUM mistake. For instance, if making use of the SQRT function to compute an equation for the square root on a negative number it will result in an error.

The #NUM error can occur when you attempt to calculate the logarithm for a negative value.

Reason 3: Iteration Formula Unable to Find Valid Result

By using an iterative formula you can perform the same calculation repeatedly again. Utilizing this IRR calculation, IRR calculates an internal return rate for various amounts flow of money. IRR formulas are regularly calculated for an output within 0.00001 percent. If IRR is not able to determine the outcome immediately after 20 shots it will end up with a #NUM error.



How do You Fix the #NUM Error in Excel?

  • Fixing #NUM! error in cases where the calculation isn’t possible

In the event that you’ve requested Excel to carry out an inexplicably difficult calculation, you’ll get an #NUM! error. The solution is well to make the calculation feasible.

Let’s take the square root problem that we talked about earlier. If you’ve got a set of numbers from a list that you’d like to find to take a square root from and then you’ve applied the formula below on the data.

=SQRT(A2)

The negative numbers have slipped into your spreadsheet, causing an #NUM! error in your other flawless spreadsheet.

After you’ve gotten yourself back from the anxiety of being able to see the #NUM! mistake, think rationally. Because it is impossible to determine how to find the square root in a negative number you have to transform it into a positive. How can you get it to be positive? It’s the ABS function.

Here’s the formula you must apply to correct the #NUM! error.

=SQRT(ABS(A2))

The ABS function transforms the negative value to an optimistic one. If you input the value as positive in SQRT SQRT feature, it will be working fine. Easy-peasy, eh?

  • Fixing #NUM! Error When the Number is Either too large or too small.

The issue that is… it’s impossible to solve this issue. If your output is big or small for Excel then you’ll have to alter your input in order so that the output value is within the range that is acceptable. For example, suppose you’ve completed the following operation on the Excel sheet:

=100^500

There’s no way of Excel will be capable of displaying this value which is why it will give an #NUM! error. It’s impossible to display the results therefore you’ll have to alter the output.



An Incorrect Function Argument

Sometimes you’ll see the #NUM! error if you input an incorrect input to a variable argument. For instance, the function DATEDIF will return what is the difference in two dates within different units. It is based on three arguments, such as this:

class=token>style=’box-sizing: border-box’>=style=’font-family:”var(–font-mono)”,serif’>DATEDIF style=’box-sizing: border-box;color:var(–color-white)’>class=token>(style=’font-family:”var(–font-mono)”,serif’>start_datestyle=’box-sizing: border-box;color:var(–color-white)’>class=token>,style=’font-family:”var(–font-mono)”,serif’> end_datestyle=’box-sizing: border-box;color:var(–color-white)’>class=token>,style=’font-family:”var(–font-mono)”,serif’> unitstyle=’box-sizing: border-box;color:var(–color-white)’>class=token>)style=’font-size:12.0pt;font-family:”var(–font-mono)”,serif’>

If inputs meet the requirements, DATEDIF returns the time between dates within the specified unit. If however, the beginning date is higher than the end date, DATEDIF returns the #NUM error. In the below screen, you will notice that the formula functions perfectly up to row 5, in which the start date is more than the date of its end. In D5 the formula returns #NUM.



The Iteration Formula Cannot Get An Outcome

Excel will display the error code #NUM when formulas use an algorithm that iterates in a way, like RATE or IRR but cannot come up with a solution. Use the following steps to modify the number of times Excel repeats formulas:

  • Select File.
  • In the view of the backstage On the backstage view, click the Options.
  • An Excel Options dialog box will be displayed.
  • Select Formulas on the left side.
  • In the Calculations options section Select the Iterative calculation checkbox.
  • Then, in the Maximum iterations box, enter the number of times you would like Excel to calculate. The greater the number of iterations, the more time Excel requires for the calculation of the worksheet.
  • Then, in the Maximum Change box, write the amount of deviation that you’re willing to take into consideration between calculations. The smaller the amount greater the accuracy of the calculation and the longer Excel will require to calculate the worksheet.


Final Thoughts

That’s it. You now know more of the reason the NUM error is displayed and what you can do to resolve this error. Best of Luck!