How to Remove Number Error in Excel (3 Ways)

While working in an excel Worksheet, you may meet error values in using formulas and other situations. Among those errors, number error is quite common in this aspect. Excel shows this error when a function or formula contains invalid numeric values. This happens mainly because of entering a numeric value using a data type or a number format that’s not supported in the argument section of the formula. There are many ways to avoid this unwanted result. Today, in this article, we will discuss how to remove number error in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Easy Ways to Remove Number Error in Excel

Excel returns number error (#NUM) due to many reasons like inputting numbers that exceed limitations, wrong arguments of formulas, inputting invalid calculations, etc. In this section, we will demonstrate three different reasons for number errors and how to solve this error.

1. Correct Invalid Calculations to Remove Number Error

One of the main reasons for occurring number errors is to perform invalid or impossible calculations. When a calculation cannot be performed, it returns a #NUM error. Assume that we have a dataset of positive and negative numbers. We have to find out the square root of these numbers. But Excel will return a number error if we apply this function to negative numbers. Follow these steps below to solve this problem.

Correct Invalid Calculations to Remove Number Error

Step 1:

  • In cell C4, apply the SQRT function. The formula is,
=SQRT(B4)
  • Here B4 is the number value.

Correct Invalid Calculations to Remove Number Error

  • Now apply this function to the rest of the cells. From the screenshots, we can see that the function returned the number error value for the negative numbers.

Correct Invalid Calculations to Remove Number Error

Step 2:

  • To remove this number error, we can nest another function within the SQRT Using the ABS function, the final formula becomes,
=SQRT(ABS(B6))

Correct Invalid Calculations to Remove Number Error

  • Now press ENTER and our number errors are removed.

Correct Invalid Calculations to Remove Number Error

Read More: How to Remove #DIV/0! Error in Excel (5 Methods)


2. Fix Too Big or Small Number to Remove Number Error

Excel has limits for the smallest and the highest numbers you can use. If you try to work with numbers outside this range, you will receive a #NUM error. Let’s learn how to solve this error.

In our data table, if we input too big numbers like 5^500, it will exceed the limit.

Fix Too Big or Small Number to Remove Number Error

As a result, it will return a number error.

Fix Too Big or Small Number to Remove Number Error

The same condition goes for inputting a too small number.

Fix Too Big or Small Number to Remove Number Error

Excel has no such functions to work out this problem. So, if you present this number in a scientific format, then you can easily show this number in your worksheet.

Fix Too Big or Small Number to Remove Number Error

Read More: How to Remove Error in Excel (8 Methods)


Similar Readings


3. Resolve Function Argument to Remove Number Error

Number errors also can happen due to incorrect function arguments. There are many functions that can cause this error. Let’s explore this section to learn about those circumstances.

3.1 Number Error Due to DATEDIF Function Argument

The DATEDIF function returns the time between dates in the unit specified. However, if the start date is greater than the end date, DATEDIF returns the #NUM error. The solution to this problem is given in the steps below.

Number Error Due to DATEDIF Function

Step 1:

  • In this step, we will find out the remaining days in the data table using the DATEDIF The function is,
=DATEDIF(B4,C4,"d")
  • Here B4 is the start date, C4 is the end date and “d” is the date remaining.
  • Press ENTER to apply this function. Now the function has returned a number error for the erroneous data input.

Number Error Due to DATEDIF Function

Step 2:

=IFERROR(DATEDIF(B4,C4,"d"),"Check For Error")
  • Here the IFERROR function will return Check For Error if there is a number value.

Number Error Due to DATEDIF Function

  • Press ENTER and apply the formula to the rest of the cells to get the final result.

Number Error Due to DATEDIF Function


3.2 Number Error Due to Iteration Formula

Some Excel functions like the IRR, RATE, and the XIRR functions rely on iteration to find a result. Excel limits the number of iterations allowed to increase performance. If no result is found before this limit is reached, the formula returns #NUM error. Imagine we are given a dataset of cash flow to find out the IRR value of these values. We will find out the reason to get the number error and the way to remove it here in this method.

 Iteration Formula

Step 1:

  • Apply the IRR function in cell E4. The function is,
=IRR(C4:C10)
  • ENTER to get the result.

 Iteration Formula

Step 2:

  • If you change the starting year cash flow into a negative number, then you can remove the number error.

Number Error Due to Iteration Formula

  • Now press ENTER to remove the error and get the result.

 Iteration Formula


3.3 Number Error Due to the RATE Function

Another cause of number error is also a financial formula. When you use the RATE function you may receive a number error due to wrong input. Suppose in a dataset, different time periods and their values are given. We have to find the rate value of these inputs.

 the RATE Function

Step 1:

  • In cell E4, apply the rate function. The function is,
=RATE(C4,C5,C6)
  • Where C4, C5, C6 are the values of the time periods.

the RATE Function

Step 2:

  • The reason for getting a number error is that the Loan Amount needs to be negative to apply this function. So make the Amount of Loan value from positive to negative.

 the RATE Function

  • Hit ENTER and the number error is removed.

 RATE Function

Read More: How to Remove Formulas in Excel: 7 Easy Ways


Things to Remember

⏩ The RATE function works within the limit only when the loan amount is negative.

⏩ You can increase the iteration limit of Excel to avoid number errors when you are using the IRR, RATE, or XIRR formula. To do that, go to

File → Options → Formulas tab → Calculation options → Enable iterative calculation

In the Maximum Iterations box, type the number of times you want Excel to recalculate. In the Maximum Change box, type the amount of change you’ll accept between calculation results


Conclusion

In this guide, we have gone through three different approaches to remove number error in excel. You are most welcome to comment if you have any questions or queries. Leave your feedback in the comment section. Thanks for visiting ExcelDemy. Keep Learning!


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo