Asikul Himel
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.
Step 1:
- In cell C4, apply the SQRT function. The formula is,
=SQRT(B4)- Here B4 is the number value.
- 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.
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))
- Now press ENTER and our number errors are removed.
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.
As a result, it will return a number error.
The same condition goes for inputting a too small number.
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.
Read More: How to Remove Error in Excel (8 Methods)
Similar Readings
- How to Remove Blank Rows in Excel Pivot Table (4 Methods)
- How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)
- Remove Symbol in Excel (8 Ways)
- How to Remove Percentage Symbol in Excel (4 Ways)
- How to Remove Drop Down Arrow in Excel (6 Methods)
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.
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.
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.
- Press ENTER and apply the formula to the rest of the cells to get the final result.
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.
Step 1:
- Apply the IRR function in cell E4. The function is,
=IRR(C4:C10)- ENTER to get the result.
Step 2:
- If you change the starting year cash flow into a negative number, then you can remove the number error.
- Now press ENTER to remove the error and get the result.
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.
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.
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.
- Hit ENTER and the number error is removed.
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!
