[Fixed!] Iterative Calculation Not Working in Excel

For research purposes, most of the time you may need to do an Iterative calculation. Sometimes, you may face the problem that you need to iterate but Excel is not working for the Iterative calculation. In that case, this article is going to be an important one for you. Today, I will describe along with the solutions why Excel Iterative calculation not working.


Download Practice Workbook

You can download the practice workbook from here:


2 Issues with Solutions When Excel Iterative Calculation Is Not Working

Here, I will describe 2 suitable solutions for why Excel Iterative calculation not working. In addition, for your better understanding, I’m going to use the following dataset. Where I want to sum up the SIN function and COS function with the same value of X using the SUM function.

Excel Iterative Calculation not Working

Furthermore, I repeat the whole process for the output from the E5 cell. At this time, I want the value of X for which the summation of the Sin and Cos function will be very close to the previous one. Basically, which is an Iterative process. Where the result will be converging.


1. By Changing Manual Calculation to Automatic

Sometimes, you try the Fill Handle icon for Iterative calculation, but you may see that all the values are remaining the same. Which means the formula is not responding to the rest of the cells.

Actually, this is happening because you may keep on the Manual Calculations option in the Excel workbook. Furthermore, below, I have attached an image of it.

Excel Iterative Calculation Not Working for keeping Manual Calculation

As a result, you will see that there is no change in the calculation. Which means your calculation remains unoperated.

Now, let’s talk about the solution.

Solution:

  • Firstly, from the Formulas tab >> go to Calculation Options.
  • Secondly, tick mark on the Automatic option.

Changing Manual Calculation to Automatic as Excel Iterative Calculation not Working

Finally, you will see that your calculation is auto-updated. Furthermore, the result from B17 is converging.

Read More: How to Use Fill Handle in Excel (4 Quick Tricks)


2. Enable Iterative Calculation from Excel Options

There is a built-in program in Excel to do the Iterative calculation. Moreover, through this, you don’t need to do the Iteration by dragging the Fill Handle icon. Here, by enabling the Iterative calculation feature you can just get your iterated result at a glance.

So, you should make sure that the Iterative calculation feature enables or not.

  • Firstly, to do so, go to the File ribbon.

Enable Iterative Calculation from Excel Options

At this time, a new window will appear.

  • Then, go to the Options.

As a result, a new dialog box named Excel Options will appear.

  • After that, from the Formulas menu >> tick mark on Enable iterative calculation.
  • Then, press OK.

So, by doing this now you can use the Excel iterative calculation against not working the iterative calculation.

  • Then, write the added value in the B5 cell.

  • After pressing ENTER, you will see the iterated values at a glance.

Result of Enabling Iterative Calculation from Excel Options

Read More: How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)


How to Fix When Circular Reference Is Not Working in Excel

There is a term in Excel named Circular reference. Here, the circular reference is a formula that returns the same or another cell numerous times in its sequence of calculations, resulting in an infinite loop that severely slows down your spreadsheet.

Furthermore, to illustrate circular reference more clearly I will use the following dataset. The dataset consists of Sales and Profit for some customers. Suppose, I need to calculate the total amount of sales in cell C12.

Now, If you accidentally select the cell range (C5:C12) you might not get the result that you are expecting. Basically, when you include this C12 cell in the function itself, then it is called a circular reference.

Circular Reference Not Working in Excel

Now, let’s take a look at the steps how to solve this problem.

Solution:

  • Firstly, follow the steps of method-2 and open the dialogue box named Excel Options.
  • Secondly, from that box select Formulas and check the option Enable iterative calculation.
  • Thirdly, set the value 1 for Maximum iterations. Here, the value 1 indicates that the formula will iterate only once through the cells C5 to C11.
  • Lastly, press OK.

Finally, you will not get any circular reference errors in cell C12. It returns the total sales amount in cell C12.

Read More: How to Add or Subtract Based on Cell Value in Excel (3 Ways)


Practice Section

Now, you can practice the explained method by yourself.

Practice Section for Excel Iterative Calculation


Conclusion

I hope you found this article helpful. Here, I have explained 2 possible solutions for why Excel Iterative calculation not working. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo