Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Circular References > Setting up
Deze pagina in het Nederlands

Working with Circular references in Excel

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 converges

    A 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 diverges

    When 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 oscillates

    The model keeps switching between two (or more) end results.

  4. The calculation results do not change, but are incorrect

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

For this I took an example from John Walkenbach’s Excel 2010 Bible. A company donates a percentage of its profit after taxes to charity. But since donations to charity are tax-deductible, the donation depends on the tax, which in turn (partly) depends on the donation. A circular reference!

Worksheet setup

The figure below shows a spreadsheet with the formulas in place:

Circular reference calculating donation

The basic principle of the VBA code is that it looks for named ranges in the file which match names like Iter000, Iter001, Iter002, ... The number is unlimited.

To set the model up for the VBA handled iteration these steps are needed.

General settings needed:

The model changes needed:

Now you have broken the circular reference chain. This is what my example looks like, with the formulas in place:

Worksheet setup, formulas

The VBA code (see below) takes the Value from the named range IterXXX and copies it onto the cell to the immediate right of the named range. The code detects a calculation of Excel and automatically repeats that calculation after copying the IterXXX results to their right. It does so the number of times set up in Iterations.

I have also set up a conditional format on range A8:E8:

Worksheet setup, Conditional format

The VBA code does not check whether the condition(s) is (are) met, this is handled by the conditional formatting. It is quite easy to update the VBA code to check whether all conditions are met at the end of an iteration and if not, display a warning message.

Download

Here is a sample file for you to download.

The VBA code

First I'll just list all code. Below you will find an explanation...

'-------------------------------------------------------------------------
' Module    : mIterate
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 10-02-2015
' Purpose   : Handles iteration of a (set of) circular reference(s)
'-------------------------------------------------------------------------
Option Explicit

Dim mlIterations As Long
Dim mlMaxIterations As Long

Const APP_NAME As String = "VBA assisted iteration example"

Public Sub EnableIterations()
    Application.OnCalculate = "HandleIterations"
End Sub

Public Sub DisableIterations()
    Application.OnCalculate = ""
End Sub

Public Sub HandleIterations()
    DisableIterations
    mlMaxIterations = ThisWorkbook.Names("Iterations").RefersToRange.Value
    If mlIterations >= mlMaxIterations Then
        mlIterations = 0
        Application.StatusBar = False
        EnableIterations
        Exit Sub
    Else
        mlIterations = mlIterations + 1
        Application.StatusBar = "Calculating circular references. Iteration # " & mlIterations
        CopyIterationValues
        Application.Calculate
        HandleIterations
    End If
    EnableIterations
End Sub

Private Function HasIterationFinished() As Boolean
    Dim oName As Name
    Dim oWs As Worksheet
    HasIterationFinished = False
    For Each oName In ThisWorkbook.Names
        If LCase(oName.Name) Like "iter###" Then
            With oName.RefersToRange
                If CStr(.Offset(, 3).Value) = "1" Then
                    HasIterationFinished = True
                    Exit Function
                End If
            End With
        End If
    Next
End Function

Private Sub CopyIterationValues()
    Dim oName As Name
    For Each oName In ThisWorkbook.Names
        If LCase(oName.Name) Like "iter###" Then
            With oName.RefersToRange
                On Error Resume Next
                .Offset(, 1).Value = .Value
                On Error GoTo 0
            End With
        End If
    Next
End Sub
Entry point routines

The code has two so-called entry point routines, routines called directly by a user action:

EnableIterations and DisableIterations. These two routines are called by the two buttons on the worksheet. They do exactly what their names state.

EnableIterations tells Excel to call HandleIterations after each completion of a calculation.

Other routines

HandleIterations first turns off the OnCalculate event by calling DisableIterations. This is done because inside HandleIterations we are asking Excel to recalculate. At that point we do not want HandleIterations to be called again as we want to control when exactly that happens.

For as long as the current number of iterations is less than the maximum number of Iterations, the routine copies the iteration result to its adjacent cell using the routine called CopyIterationValues. If the maximum # of iterations has been reached, the number of iterations is reset to zero and the iteration chain is terminated.

Note that the code also contains a function HasIterationFinished. Currently this function is not used, but you could include the function in HandleIterations to show a message to the user when the iteration has not reached anend result matching your criteria. Note that HasIterationFinished assumes a setup where the test-result is in a cell three columns to the right of the IterXXX cell. It is relatively easy to change this by naming the test cells to something like TestXXX.

 


 


Comments

All comments about this page:


Comment by: Gene Wesseler (6/21/2012 11:23:38 PM)

I accidentally had introduced a circular reference in a Window 7 excel folder. I fixed it, but the circular ref. warning comes up every time I open that folder. Even there search for an error says there is no error. How do I get rid of this annoying error message.
Thanks,
Gene

 


Comment by: Jan Karel Pieterse (6/22/2012 2:02:20 PM)

Hi Gene,

The error checking dropdown has a specific entry for circular references, use that to track which circle it finds.

 


Comment by: simon (8/21/2012 7:54:38 AM)

Hi Jan
Thanks for the article and the tips that go with it, particularly regarding having a recovery system to effectively turn off the circular referencing (and allow the spreadsheet to recover). I've previously worked with a very large spreadsheet that was modelling an industrial refinery. The design included many (>50) circular references to allow the whole spreadsheet to converge. Occassionally it would crash and send all values to #Div/0! and it would have been useful to have a recovery out. I would close the spreadsheet without saving which is quite risky. For this reason I always stored several functioning back-ups.

I actually quite liked the power of circular references, and it is nice not to have to rely on vba code to do the job. One comment I would make regarding convergence, which definitely becomes an issue with multiple circular references, is to implement tuning parameters. So these are effectively factors say 10, 100, 1000 etc that will control the rate of change per iteration within a given circular referenced cell.

In terms of understanding how to set these parameters process control theory has a rule of thumb that cascade (or dependent controllers) should have rate of change difference of a factor of 5 (at a minimum). So a low level controller will make adjustments at a given rate, a controller that sits on top of it should respond 5 times more slowly. So it is a matter of working out which are the faster controllers and which are the slower ones. The faster controllers will be those that tend to have impacts more locally, and the slower ones those that will tend to cause interactions throughout the model. For the most complex models you'd probably find that there would be 3 layers of controllers speeds (hence tuning factors of 10, 100 and 1000 as an example). For safety I would tend to use a factor of 10 rather than 5, just to give a greater guarantee of convergence, at the expense of a slower convergence time.

 


Comment by: Jan Karel Pieterse (8/21/2012 10:55:27 AM)

Hi Simon,

Nice addition, thanks!

 


Comment by: simon (11/4/2013 6:48:30 AM)

A follow-up comment to the one 8/21/2012 7:54:38 AM. The refinery model had chemical properties implemented as functions. This is a convenient implementation from a programming point of view as the functions can be easily called for each process stream; inputs to the functions being the individual chemical components of the process stream. However with circular references operating to converge the model this meant highly frequent access of VBA across hundreds of process streams. The model took about 30 minutes to solve.

VBA/function processing is very slow relative to in-cell formula processing (~2 to 3 orders of magnitude). So a recommendation would be to avoid using VBA/functions within the spreadsheet if the code will access the VBA/funtions during the convergence. Obviously this only applies where solving time is becoming an issue; when modelling a very large system like this.

 


Comment by: Jan Karel Pieterse (11/4/2013 9:06:06 AM)

Hi Simon,

Useful comment indeed. Although I must say that UDF's need not be slow. There are situations where UDF's are faster than worksheet functions. This highly depends on the way the UDF was programmed.

 


Comment by: Sam (3/28/2014 7:16:10 PM)

Hi Jan,

I am glad I found your website, very informative! Thank you!.

I am working on creating a circular reference to run an iteration. The calculations are converging to zero but with a negative target value. How do I get the iteration to converge resulting in a positive target value?
My circular ref is B1 = IF(B1 = 0, 1000, B1-E1/1000)
E1 is the value that is supposed to be converged to zero. B1 is used in the calculations that results in E1. B1 is supposed to increase in value such that my E1 converges to zero.
If I change the ref formula to B1 = IF(B1 = 0, 1000, B1+E1/1000), the target cell never converges.

Could you please let me know where I am doing wrong? I really appreciate your assistance.

Thanks in advance.

Regards,
Sam

 


Comment by: Jan Karel Pieterse (3/31/2014 6:54:29 AM)

Hi Sam,

What other formulas "build" your circle?

 


Comment by: Sam (4/8/2014 7:59:47 PM)

Hi Jan,

I was able to resolve it. My formulas associated with the above circular reference had a glitch. I was supposed to set the criteria (E1/1000) on a different cell.

Thank you!

Regards,
Sam.

 


Comment by: Sam (4/14/2014 9:31:38 PM)

Hi Jan,

Hope you are doing well.

I have been using circular reference for recycle streams and mass balance. It works OK. But when I change my input values, I get #NUM! error and in order to make the sheet working I have to remove all recycle streams, recalculate, reconnect the recycle streams and then calculate the sheet. Is there a way i can avoid doing all these and not get any #NUM! errors?

I really appreciate your help.

Thank you.
Regards,
Sam.

 


Comment by: Jan Karel Pieterse (4/15/2014 10:06:16 AM)

Hi Sam,

Your #NUM problem is a very good example of why you would need some logic to be able to break the circles and set up initial values for them using the example shown above.
Basically all it takes is two extra cells; one with some value in it to either make or break the circle and the other to set an initial value.

A good initial value is the value that is currently in your cell while the circle is still showing valid results.

Suppose you reserved Cell A1 as the switch and A2 as the initial value cell, then the first thing you do is enter appropriate values in those two cells. In A2 enter the value currently in the cell you are going to use to put the switch in. Then adjust the cell inside the circle to:

=IF(A1=1,A2,OriginalFormulaGoesHere)

Now as soon as you enter a 1 into cell A1, the circle is broken en the cell with this formula now switches to using cell A2. Remove the 1 from A1 and the circle is reinstated.

 


Comment by: Ted Eggleton (6/16/2014 9:17:43 AM)

I would like to use a circular reference to calculate the interest owing on a balance and then add it to that balance. This performs the calculation correctly when I set the number of iterations to 1 and the maximum change to 1.

The problem is that it screws up the rest of the workbook. Some form of calculation is done when I refer one cell back to another, i.e. B5="previous sheet"B50. I would expect B5 to have the same value as B50 on the previous sheet but it does not.
I have no doubt there is some long and convoluted explanation but is there a way to make circular references work in a way that does not affect other areas of the workbook?

What would be ideal is if reiteration could be applied to a set of specific cells rather than being applied to every workbook. Even if it could be restricted to the workbook in question it would be a help in preventing gotchas in other workbooks.

 


Comment by: Jan Karel Pieterse (6/16/2014 9:38:04 AM)

Hi Ted,

I would probably take a different approach: not use a circular reference to do this. Excel has lots of financial functions to do a calculation like this.

 


Comment by: Ted Eggleton (6/16/2014 9:49:06 AM)

Thank you for a prompt response but I have to say it is not very helpful!:-(
This is a simple A=A+1 type of requirement that works in itself but screws up the rest of the workbook and by implication all other workbooks accessed by that particular Excel instance.
Essentially, what you are saying is don't use reiteration.

 


Comment by: Jan Karel Pieterse (6/16/2014 10:27:12 AM)

Hi Ted,

Let me make that a bit more precise: Do not use circular references unless there is no other way.

Many calculations requiring circular references can be done using built-in worksheet functions that do the required iterations internally. Look in help at the financial functions (like PMT), I am quite sure there should be one there that does the calculation you require without needing to set up iterations.

 


Comment by: Ted (6/20/2014 10:26:42 AM)

Problem solved. Many thanks for your assistance

 


Comment by: Norman Hicks (9/25/2014 10:52:49 PM)

When do you plan to write the article about passing iterative control to VBA? I use circular references to model various industrial processes, and I would like your ideas regarding how to accomplish this. One situation that frequently occurs is having "nested" loops. I'd potentially like to be able to declare a priority level that would allow "inner loops" to iterate to convergence before proceeding with the calculation of given iteration of the "outer" loop.An example would be a flow balance where the "outer" loop would be the balance, and an "inner" loop would be a recursive determination of the applicable friction factor. Thus for each change in conditions constituting an iteration of the "outer" loop I would need to compute the friction factor by looping it to convergence. We also run into this situation where we assemble plant wide models where each machine may have a loop, and the machines are connected in some type of circuit such that to complete an iterative loop for the plant each machine would need to loop to convergence as encountered before proceeding with the iteration for the plant. I really appreciate your work and everything that you have shared on this site. Any ideas or assistance are greatly appreciated.

 


Comment by: Jan Karel Pieterse (9/26/2014 11:46:41 AM)

Hi Norman,

To be honest, it sort of disappeared under my radar :-)
My biggest problem is that I do not have a good example model to demonstrate the technique with. If anyone does have a model (perhaps not as complex as yours :-) ), please sahre it with me so I can have a look at it and perhaps use it as the basis for the article!

 


Comment by: Eric (2/11/2015 2:25:33 PM)

Hi Jan Karel,

Obviously, your example is not about calculating in itself, but to provide an example for the benefit of circular reference.

Based on this example, you can also calculate the amount of charity with a "simple" formula:

Charity = ((100% - Taxpercentage)* Revenu) -((100% - Taxpercentage)*Expenses) / ((100%/Charity-percentage)+(100%-Taxpercentage)



With regard to VBA would recursion may also be a method?
Have not tested it myself, just an idea, I have my iterative calculation (always) disabled

Anyways; Thanks for sharing!

 


Comment by: Eric (2/11/2015 2:35:47 PM)

forget my remark/comment about recursion, I see it's already in there ;-)

 


Comment by: Jan Karel Pieterse (2/11/2015 4:10:56 PM)

Hi Eric,

Of course you are right. I just wanted to share this to show an alternative method to control the iteration. The method shown here makes it possible to do problem-specific things, like detecting instabilities in the iteration.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].