[Fixed!] Iterative Calculation Not Working in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Excel Iterative Calculation Is Not Working: 2 Issues with Solutions

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 remain 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 Allow Circular Reference in Excel


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 Find a Circular Reference in Excel


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 Fix Circular Reference Error in Excel


Practice Section

Now, you can practice the explained method by yourself.

Practice Section for Excel Iterative Calculation


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 2 possible solutions for why Excel Iterative calculation not working. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Circular ReferenceCell Reference in ExcelExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo