Excel iterative calculation and circular reference
To understand what is Excel iterative calculation, you should first understand what is circular reference.
Circular reference happens when you type a formula in one Cell which refers to another Cell, but that Cell also refers back to the Cell.
For example,
Cell A1: = 1+A2
Cell A2: = 1+A1
The above formula refer to each other, this is called circular reference (refer to each other forming a circle). The formula will fail and the below error box will prompt.
Excel Iterative calculation
Since Excel 2007, a new function was introduced to allow circular reference.
Below is a screenshot of Excel 2013, other versions also have the check box “Enable iterative calculation”, check the box to enable.
“Maximum Iterations” define how many times you want to loop the calculation.
“Maximum Change” define the maximum amount of change you will accept between recalculation results, the smaller the slower but more accurate.
To illustrate how iterative calculation works, follow the below steps.
1) Disable “iterative calculation”
2) Type the following formula
Cell A1: = 1+A2
Cell A2: = 1+A1
3) Set Maximum Iterations to 10, leave Maximum Change default, check the box to enable iterative calculation
Result
A1 will immediately show 19 and A2 will show 20. The underlying mechanism for each iteration is shown below.
| Iteration | A1 value | A2 value |
| 1 | 1 | 2 |
| 2 | 3 | 4 |
| 3 | 5 | 6 |
| 4 | 7 | 8 |
| 5 | 9 | 10 |
| 6 | 11 | 12 |
| 7 | 13 | 14 |
| 8 | 15 | 16 |
| 9 | 17 | 18 |
| 10 | 19 | 20 |
Note carefully that Workbook Calculation is set to “Automatic” by default.
Every time you edit any Cell (Worksheet_Change Event is triggered), Cell A1 and A2 will recalculate one more time based on the previous result.
That means A1 becomes 39 and A2 becomes 40 for the first recalculation.
To avoid recalculation, you should change the Workbook Calculation from “Automatic” to “Manual”. A1 and A2 will only refresh if you edit them.
Purpose of Excel iterative calculation
The above example demonstrates the mechanism behind the iteration calculation process, but why do we need it?
The purpose of it is to simulate the VBA Loop, but you don’t need to know how to write VBA.
Take For Loop as an example, the below code simulate the actions above.
Public Sub floop()
For i = 1 To 10
Range(“A1”).Value = Range(“B1”).Value + 1
Range(“B1”).Value = Range(“A1”).Value + 1
Next i
End Sub
You can even insert IF condition in the iterative calculation to tell when to stop, exactly like VBA Loop.
Cell A1: = 1+A2
Cell A2: =IF(A1+1>10,10,A1+1)
Outbound References
http://www.k2e.com/tech-update/tips/433-tip-performing-iterative-calculations-in-excel
