[Fixed!] NUM Error in Excel (4 Reasons with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn what are the reasons behind #NUM! error in Excel and show you how to solve the problem. When working with an Excel Worksheet, you may encounter errors in formulas and other scenarios. #NUM! error is a frequent type of error in this context. There are numerous strategies to avoid this undesirable outcome.  So, without delay, let’s explore the reasons with solutions of num error in Excel.


[Fixed!] NUM Error in Excel: 4 Possible Reasons with Solutions

Excel returns #NUM! Error for various reasons, including entering numbers that exceed the allowable range, incorrect formula parameters, erroneous calculations, etc. For using the Rate Function, and IRR Function you need to fix some options in Excel. If we do not fix these options, then it will show #NUM! error. In this section, we will discuss 4 distinct causes of num errors in Excel and how to correct them.


Reason 1: Big or Small Number Outside of Allowable Range

Excel has limits on the lowest and largest numbers that can be used. Every cell has a specific capacity. You can not exceed the limit. If you try to operate with numbers that are not inside this range, you will get a num error in Excel. Let’s see an example of this type.

  • In the C5 cell, write =9^1000 as we want to insert this number in this cell.
  • Then, press Enter to exit from the editing mode and see the result.

num error in excel because Big or Small Number Outside of Allowable Range

  • But, the limit has been exceeded.
  • For this reason, there is showing #NUM! instead of the original value.

  • After that, In the C7 cell, write =-5*10^1500 as we want to insert this number in this cell.
  • Then, press Enter to exit from the editing mode and see the result.
  • But, there is also a limit to the smallest number.
  • We can not insert any number outside of the allowable range.
  • For this reason, the cell is showing #NUM! error.


Solution: Write Numbers in Text Format

We can write the numbers as normal text. Then it will not show any error. Let’s correct the #NUM! error.

  • In this step, we will solve this problem.
  • Here, we will try to write these 2 numbers in the cells.
  • So, select the C6 cell and write 1×9^1000.
  • After pressing Enter you can see the number in the C6 cell.

solve of num error in excel by Writing Numbers in Text Format

  • Similarly, you can write any small number outside the allowable range.
  • Therefore, we have selected the C8 range and written down 5x-10^1500 in the cell.
  • Then, press Enter to proceed.


Reason 2: Impossible Calculation

One of the main reasons why number errors happen is because people try to do calculations that are wrong or can’t be done. When a calculation can’t be done, #NUM! error will be shown up in Excel. Let’s say we have a set of numbers that includes both positive and negative numbers. Find the square root of each of these numbers. But if we use this function on a negative number, it will give us a #NUM! error in Excel. Let’s see an example of this type.

  • In the following figure, there is a dataset of a few numbers.
  • We will find the square root of these numbers.
  • To do so, select the C5 cell and write the following formula:

=SQRT(B5)

  • After typing, click on Enter to watch the square root of the B5 cell.

num error in excel because of Impossible Calculation

Here, we have used the SQRT function to determine the square roots in Excel.
  • After that, drag down the formula in the C column with the help of the Fill Handle option.

  • As expected, the square root value should be shown.
  • But, the square root value of the negative number is showing #NUM!.
  • Here, we have done invalid calculations.
  • It is a common rule that the square root of a negative number can not be determined.
  • That’s why the cell is showing this #NUM! error.


Solution: Apply ABS Function

We can not calculate the square root value of a negative number. But, it is possible to calculate the square root value of the modulus of the number. Modulus represents the value of the number, not the sign. Let’s follow the steps below to solve this problem.

  • Firstly, select the C7 cell and write down the following formula:

=SQRT(ABS(B7))

  • Then, press Enter to proceed.

solution of num error in excel by Applying ABS Function

Here, the ABS function finds out the Absolute or Modulus value of the cell’s value. Then, SQRT function calculates the square root of the resultant value.

  • Similarly, find out the result by writing the following formula in the C9 cell:

=SQRT(ABS(B9))


Reason 3: Incorrect Arguments of Function

Number errors can also happen when the arguments to a function are wrong. This error can be caused by many functions. The DATEDIF function calculates the difference between 2 dates. But, there is a condition here in the arguments of this function. Here, the 1st argument should be greater than 2nd argument. It means that 1st date should be older than the next date. Else, it will show a num error in Excel. Let’s see an example of this type.

  • In the following dataset, you can see the start and end date of a program.
  • Now, we will calculate the difference between these 2 dates.
  • To do so, write down the following formula in the D5 cell:

=IFERROR(DATEDIF(B5,C5,"d"),"Check For Error")

  • Then, press Enter to see the result in the cell.

num error in excel because of Incorrect Arguments of Function

Here, the DATEDIF function calculates the difference between 2 dates.

  • After that, drag down the formula in the D column using the Fill Handle option.

  • As expected, the difference between the 2 dates should be shown.
  • But, some results are showing #NUM!.
  • In those entries, there are incorrect arguments.
  • In the DATEDIF function, 1st argument of date should be older than the 2nd argument of date.
  • That is why these entries are showing #NUM! error in Excel.


Solution: Insert IFERROR Function

Now, we will remove the #NUM! error in Excel. Using the IFERROR Function, we will solve this issue. Here, we will check if an error occurs. If any error occurs, then it will return that the error has occurred. Otherwise, it will return to its normal value. Let’s follow the steps to solve the problem.

  • To do so, select the D5 cell and range the following formula:

=IFERROR(DATEDIF(B5,C5,"d"),"Check For Error")

  • Then, press Enter to proceed.

solution of num error in excel by Inserting IFERROR Function

Here, the formula will return Check For Error if there is a number value. Otherwise, it will return the difference between the 2 dates.

  • Then, drag down the formula using the Fill Handle in the following D column.

  • As a result, the num error has vanished.
  • If the arguments are right, then it will show the result.
  • Otherwise, it will write instructions for Check For Error.


Reason 4: Inactive Iteration Calculation

4.1 For IRR Function

Some Excel functions, such as the IRR function use iteration to find the answer. Excel puts a limit on the number of iterations that can be used to improve performance. If this limit is reached before a result is found, the formula will return #NUM! error. Let’s see an example of this type of problem.

  • Here, we have a set of cash flow values over the period of 2010 to 2022.
  • Now, we have to find out the IRR.

num error in excel for IRR Function

  • Now, write the following formula in the E5 cell to calculate the IRR:

=IRR(C5:C11)

  • Then, press Enter to see the result.
  • But, it is showing #NUM! error.
  • Because at least 1 entry needs to be negative.
  • At least, 1 year we need to invest and in that year, cash flow will be negative.


Solution: Enable Iteration Calculation and Correct Sign of Dataset

We can solve this problem by enabling the iteration option in Excel and then checking for problems in the given dataset. Let’s follow the steps below to solve this #NUM! error in Excel.

  • Firstly, we need to activate the Iterative Calculation so that Excel can calculate the iterative problems.
  • For the calculation of IRR, we need an iteration process.
  • Therefore, click on the File tab.

  • After that, a new window will open up.
  • In this window, select Options.

Solve num error in excel by Enabling Iteration Calculation and Correct Sign of Dataset

  • Instantly, another window will open up.
  • After that, click on the Formulas panel.
  • Then, tick on the Enable iterative calculation option and click OK.
  • As a result, the iteration procedure option is activated in Excel.

  • After that, Change the Cash Flow of 2010 from 20,000 to -20,2000.
  • As a result, the IRR value is showing in the E5 cell.


4.2 For RATE Function

Some Excel functions, such as the RATE function, use iteration to find the answer. Excel puts a limit on the number of iterations that can be used to improve performance. If this limit is reached before a result is found, the formula will return #NUM! error. Let’s see an example of this type.

  • In the following dataset, different time periods and their values are given.
  • Now, you have to find the rate value for these inputs.
  • To find out the rate, write the following formula in the E5 cell:

=RATE(C5,C6,C7)

  • After that, press Enter to see the result.
  • But, the E5 cell is showing #NUM! error.
  • One is Iteration is not active in Excel or any input is wrong.
  • Here, the reason for getting a number error is that the Loan Amount needs to be negative to apply this function.
  • For this reason, we need to make the Amount of Loan value from positive to negative.

num error in excel for Rate Function


Solution: Activate Iteration Calculation and Correct Sign of Dataset

We can solve this problem by enabling the iteration option in Excel and then checking for problems in the given dataset. Let’s follow the steps below to solve this #NUM! error in Excel.

  • Firstly, we have to check if the Enabled Iteration Calculation is ticked.
  • If it is not ticked, then you must have to give a tick on it to activate Iteration in Excel.
  • In Solution 4.1, we have shown already how to activate Iteration Calculation.
  • So, after activation, change the value of the Amount of Loan in the C7 cell from 1,00,000 to -1,00,000.
  • As a result, you can see a valid result in the E5 cell showing the Rate.

Solve num error in excel by activating Iteration Calculation and Correct Sign of Dataset


Download Practice Workbook

To practice by yourself, download the following workbook.


Conclusion

In this article, we have demonstrated how to fix #NUM! Error in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. Lastly, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles


<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo