Introduction

If you’ve come to this page, chances are you’ve experienced the “Circular reference warning” popping up when you opened an Excel file or entered a formula. Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll try to demystify that message here!

The next couple of pages discuss:

Table Of Contents


Properly setting up circular references

Whereas I am no fan of using circular references, they can be beneficial to your model and really solve the problem you are trying to solve. So here is some advice on how to properly work with them.

Breaking the circles and setting initial values

In order to prevent your model from running into trouble, make sure there is a quick way to break each circle. Circular reference chains are risky in that if any of the cells inside a circle inadvertently yields an error value (e.g. a #DIV/0!), your model might not be able to recover and keeps showing error values.

What you can do is have (at least) one cell within each circle which contains an IF function, which -in turn- tests the value of a switch cell. If the switch cell contains anything, the circle is broken and a default value from another cell is taken, instead of the cell that is part of the circular reference chain.

Suppose you created this hypothetic circle:

A simple circular chain
A simple circular chain

To get the behavior I just described, I replace the formula in cell C3 with:

=IF($B$1=1,$B$2,D4)

Your sheet should now look like this:

A simple circular chain which you can break
A simple circular chain which you can break

Now test your new breakable chain by entering a 1 in cell B1. Cell C3 now will get an initial value of 10 from cell B2. Clear cell B1 to restore the circle.

You can combine the breaker cell with the initial value cell by modifying the formula a bit:

=IF($B$1=””,D4,$B$1)

This way, you can enter a starting value in cell B1. This breaks the circular chain and sets the starting value. Remove the starting value to start calculating. The disadvantage of combining the break cell and starting value cell is that you loose the starting value as soon as you start the iteration because you have to empty the starting value cell to do so.

Does your model converge?

A big problem with circular references is that you have to pay attention whether or not your model yields stable results in all circumstances. Iterative calculations can be in these states:

  1. The calculation convergesA converging calculation reaches a stable end result, where the last result does not differ more than a fraction from the one-but-last result. Basically, this difference should be less than or equal to the “Maximum Change” setting in your calculation settings.
  2. The calculation divergesWhen the calculation diverges, things get out of hand: your values keep increasing or decreasing, never to reach a stable end result. The “model” I showed above is an example of a diverging chain. A calculation with results that switch signs (alternating between positive and negative, but with an increasing absolute value) between iterations is also considered to be divergent.
  3. The calculation oscillatesThe model keeps switching between two (or more) end results.
  4. The calculation results do not change, but are incorrectThis is the hardest situation to detect, as you may be under the impression you have reached a solution to your problem. I advise you to find a way to check your results; Are they correct?

So you want to have a converging calculation, I assure you.

One way to check for converging calculation is by setting the maximum iterations to 1. That way you can check the intermediate values by hitting F9, Excel will do one iteration for each press of the F9 key. Pay attention to the results and you’ll see whether your calculation converges.

An alternative approach: have VBA control the circles

An alternative approach to monitor circular reference calculations is by handing over control of the iteration to VBA.

The method I propose here is to break the circles and use intermediate cells which are controlled by VBA to pass on their results to the other cells which used to form the circular reference chain. VBA will perform the iteration and by using some additional cells, the code can closely monitor the results and act accordingly. I plan to write a separate article on this subject at a later stage.

Table Of Contents: Go to


Courtesy: jkp-ads.com

Comments

Leave a Reply

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

4 + 5 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.