Microsoft Office Tutorials and References
In Depth Information
Circular Reference Examples
Figure 16-7: This worksheet solves two simultaneous equations.
Note the circular reference. The X cell refers to the Y cell, and the Y cell refers to the X cell. These
cells converge to display the solution:
X = –1.333
Y = 3.000
Using intentional circular references to solve simultaneous equations is more of an interesting
demonstration than a practical approach. You’ll find that some iterative calculations never
converge. In other words, successive recalculations will never hone in on a solution. For example,
consider the simultaneous equations that follow. A solution does indeed exist, but you cannot use
circular references to find it.
x = 4 – y/2
y = 3 + 2x
Using matrices is a better approach for solving simultaneous equations with Excel. See
Chapter 10 for examples.
The companion CD-ROM contains the workbook simultaneous equations.xlsx
with two sets of simultaneous equations. You can solve one set by using intentional
circular references; you cannot solve the other set using this technique.