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.
Here, we used Excel 365. You can use any available Excel version.
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.
Step 1: Using Formula to Initiate Iterative Calculation in Excel
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
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.
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.
Download Practice Workbook
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.
- How to Find a Circular Reference in Excel
- Fix Circular Reference That Cannot be Listed in Excel
- How to Remove Circular Reference in Excel
- How to Fix Circular Reference Error in Excel