Excel iterative calculation and circular reference

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.

iterative_calculation_02

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.

iterative_calculation

“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.

iterative_calculation_03

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://office.microsoft.com/en-gb/excel-help/change-formula-recalculation-iteration-or-precision-HP010054149.aspx

http://www.k2e.com/tech-update/tips/433-tip-performing-iterative-calculations-in-excel

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *