If you want to enable iterative calculation in Excel, you have come to the right place. Here, we will walk you through 2 easy and quick steps to do the task smoothly.
Download Practice Workbook
What Is an Iterative Calculation?
When repeated calculations occur until a specific numeric condition is met, it is called iterative calculation. This calculation uses previous results to solve a problem. And the calculation runs repeatedly. Iterative calculations help Excel find a solution quickly. Therefore, we need to enable iterative calculation in Excel in many situations.
2 Steps to Enable Iterative Calculation
In this article, we will describe 2 steps so that you can enable iterative calculation effortlessly. Here, we used Excel 365. You can use any available Excel version.
Step-1: Using Formula to Initiate Iterative Calculation
The following table shows the values of Cost Price, Selling Price. Here, we will enable the iterative calculation. After that, we will calculate the Other Expense and Profit.
- First, we will write the following formula in cell C6 to calculate Other Expenses.
Here, C7/4 will divide the Profit by 4 and find out the Other Expense. The result will be $0.
- After that, press ENTER.
- Afterward, we will type the following formula in cell C7.
Here, C5-C4-C6 returns the Profit in cell C7. This subtracts the Cost Price and the Other Expense from the Selling Price. The result is $0.
- Then, we will press ENTER.
As soon as we press ENTER, a warning arrives. This indicates that we have to enable iterative calculation.
Here, we will see a join arrow mark colored blue in cells C6 and C7. This indicates that iterative calculation will occur in these cells.
Read More: How to Allow Circular Reference in Excel (With 2 Suitable Uses)
Step-2: Using Excel Options to Enable Iterative Calculation
Now, to resolve the circular reference warning, we will enable iterative calculation from Excel Options. And this will calculate the Profit and Other Expenses.
- First, we will go to the File tab.
- Then, we will select Options.
An Excel Options window will appear.
- After that, we will select Formulas.
- Afterward, we will mark Enable iterative calculation.
Here, you can set Maximum Iterations and Maximum Change according to your needs. We keep these as it is.
- Then, click OK.
Finally, we can see that a calculation took place in cells C6 and C7. And we can see the values for Other Expense and Profit.
Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)
Things to Remember
- You should limit the iteration number according to your needs. While more precise results come from a higher iteration count, this can take a huge amount of calculation time.
- Along with that, if we do not enable iterative calculations in Excel, a warning will appear. This is because circular references are most of the time considered user errors.
Here, we tried to show you how to enable Iterative Calculation in Excel. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website ExcelDemy to explore more.