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

## Download Practice Workbook

To practice by yourself, download the following workbook.

## 4 Possible Reasons with Solutions If You Find #NUM! Error in Excel

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

**Read More: ****Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)**

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

**the 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))`

**Read More: ****Errors in Excel and Their Meaning (15 Different Errors)**

**Similar Readings**

**How to Fix #REF! Error in Excel (6 Solutions)****[Fixed] Excel Print Error Not Enough Memory****On Error Resume Next: Handling Error in Excel VBA****REF Error in Excel (9 Suitable Examples)****Reasons and Corrections of NAME Error in Excel (10 Examples)**

### 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 error has occurred. Else, 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. Else, it will return the difference between the **2 **dates.

- Then, drag down the formula using
**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.
- Else, it will write instructions for
**Check For Error**.

**Read More: ****How to Remove Value Error in Excel (4 Quick Methods)**

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

**Read More: ****VALUE Error in Excel: 7 Reasons with Solutions**

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

**Read More: ****How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)**

## 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. To read similar articles, check out the **ExcelDemy **website. Lastly, please use the **comment section** below to post any questions or make any suggestions you might have.

## Related Articles

**[Fixed!] Null Error in Excel (3 Possible Solutions)****How to Calculate Tracking Error in Excel (with Detailed Steps)****[Fixed!] Error Messages in Excel (10 Practical Solutions)****[Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)****Find Reference Errors in Excel (3 Easy Methods)****How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)****Excel VBA: Turn Off the “On Error Resume Next”**