Circular References In Excel

If you'd like live help, schedule some time on Google Helpouts

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!

  A
1 10
2 10
3 =SUM(A1:A3)

John Walkenbach illustrates the concept of circular references very neatly in his book Excel Formulas - you can see a similar example here.

Be aware, 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 as 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 and error: 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)

 

You need to allow circular references (AKA Iteration) at least for a little while.

Go to the Office button at the top left and click it to reveal Options. Go to the Formulas section and find Iterations as shown below.

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

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.