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.

**Table of Contents**hide

## 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!