How to Remove Number Error in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

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. Correcting Invalid Calculations to Remove Number Error in Excel

One of the main reasons for occurring number Excel 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 the 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


2. Fixing Too Big or Small Number to Remove Number Error in Excel

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


3. Removing Number Error by Resolving Function Argument in Excel

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 in Excel

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:

  • We will remove this number error using the IFERROR function. In that case, the formula is,
=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 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 in Excel

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


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


Download Practice Workbook

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


Conclusion

In this guide, we have gone through three different approaches to remove number errors in Excel. You are most welcome to comment if you have any questions or queries. Leave your feedback in the comment section.


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.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo