Circular References In Excel
We've probably all run into the problem of circular references.
Typically it occurs when we're dragging the mouse down through a column
of numbers to sum and we inadvertently include the sum itself in the
numbers to sum. You never get to an answer since every time you add all
the numbers you change one of the numbers you're adding together! John
Walkenbach illustrates the concept of circular references very neatly in
his book Excel Formulas - you can see his
example here.
However, not all circular references are bad! Many real world problems
exist where two interdependent formulas must be solved at once.
Electrical engineers and physicists run into these problems regularly
and so do financial planners. Consider a capital budget whose total
expenditures depend on interest rates, but whose interest rates depend
on total expenditures. Planners solve these problems by trial one
interest rate, checking the budget, then try again with a quarter
percent change in rates up or down, then try again...
Excel can do the same thing if you allow it to solve circular references
rather than disallowing them. Consider a simple example where you charge
a premium on the total of an invoice. But the total includes the
premium! See the worksheet below with figures on the left and
formulas on the right.
Cell E3 refers to Cell E4 which refers to Cell E3... How can you figure out this puzzle?
| Est. Gadgets Cost |
$500.00 |
|
Est. Gadgets Cost |
500 |
| Est. Widgets Cost |
$500.00 |
|
Est. Widgets Cost |
500 |
| Rush Premium |
$111.11 |
|
Rush Premium |
=10%*E4 |
| Total |
$1,111.11 |
|
Total |
=SUM(E1:E3) |
Go to Tools > Options in Excel. Select the Calculation tab and turn on
Iteration by clicking the check box as shown below. Notice the two boxes
associated with Iteration: Maximum Iterations and Maximum change.
Above we
mentioned that a human planner might try some calculations with one interest
rate and then try them again with a quarter percent change. That quarter
percent would be the Maximum change: how much should I change my values for
the next try. You can probably guess that the Maximum iterations is how long
you want Excel to keep trying to find an answer. One hundred is pretty small
for a modern computer. You probably wouldn't notice a time lag for 10,000
tries but you'll have to experiment with your own computer and your own
problems.
You really should turn Iterations off if you are going to share
a file with other people. If they enter one of the BAD kind of circular
references, Excel will try very hard to make it work and when it can't,
they'll get an error message. If you send off a large spreadsheet with a
number of circular references and leave Iterations turned on, someone else
may not be able to open the workbook if they have a computer with less power
than yours.
The best practice is: once you've found a suitable answer to your problem, copy the
formulas and use Paste Special...Values to overwrite the circular reference.
There are other more powerful tools you can use in Excel like Goal Seek and
Solver for solving iterative equations for capital budgeting, transportation
problems and other linear programming issues.