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.

**Table of Contents**Expand

## [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.

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

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

**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.

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.

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.

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**.

- 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**.

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

#### 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**.

**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

- How to Remove #DIV/0! Error in Excel
- How to Calculate Mean Squared Error in Excel
- How to Calculate Root Mean Square Error in Excel
- How to Remove Number Error in Excel
- How to Remove Value Error in Excel
- [Fixed!] VALUE Error in Excel
- Excel Error: The Number in This Cell is Formatted as Text

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