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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

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 > Calculation settings
Deze pagina in het Nederlands

Working with Circular references in Excel

Circular references and calculation settings

If you want to work with circular references, the calculation settings of Excel are very important. This page gives you some pointers!

Calculation settings

The first thing that needs to be done if you want to assure your model works, is to turn on iterative computation of the file.

Iteration settings in Excel 2010
Iteration settings in Excel 2010

It is up to you to decide how many iterations you want Excel to do before it stops, or what precision you need before Excel stops (whichever comes first). As soon as you check the box "Enable Iterative calculation", Excel will do a calculation of your model. After saving the file, if you open the file again you should no longer get the circular reference warning message.

If you are troubleshooting your calculation, set Maximum Iterations to 1. This gives you the opportunity to step through the calculations one at the time by repeatedly hitting the F9 key.

Which calculation settings apply

I often get this question: I have checked the "Enable Iterative calculation" box on my file. Why do I still get the circular reference warning? To be able to understand what causes this it is important to know how Excel handles its calculation settings.

Application wide settings

Calculation settings are application-wide. That is, if workbook A needs manual calculation and workbook B needs automatic calculation and you have both workbooks open, Excel's current setting will apply to both workbooks. The same goes for the iterative calculation settings: they apply to all workbooks in your Excel session.

When you save a workbook, whichever calculation setting was applied at that time is saved with the workbook.

First-come first-serve

Excel will apply the calculation settings of the first workbook you open in a session. So if you first open workbook A (which had iteration disabled when it was last saved) and then Workbook B (with iteration enabled when it was last saved), Excel will keep iteration disabled. This explains why you do get the circular reference warning on that workbook.

Warning: When you save your workbook, the calculation settings that are currently in effect are saved with the file. This means that if you have previously set up iterative calculation and the max iterations and max change, these settings may be overwritten with the current settings.

Making sure you have the calculation settings you need

There are several ways to ensure your workbook calculates as expected:

Always open as the first workbook

Well, that one is obvious enough. Of course if your model is used by other people as well, this is not exactly fool-proof. Your users would have to be made aware of this situation, but chances are very high your calculation settings will get overwritten at some point, making your model unreliable. And even if you're the sole user, this is a big risk.

Use a bit of VBA to control calculation settings

A more reliable way to control the calculation settings is by adjusting them when your workbook loads. This means you will have to add macro's to your file, but this is straightforward enough.

I assume the file with the circular references is already open.

Open its ThisWorkbook module by double-clicking on it in the project explorer as shown below:

The Project explorer in the VBA Editor

The Project explorer in the VBA Editor

Paste this code into the code window that opens up and modify the calculations settings so they match what you need.

Option Explicit

Private Sub Workbook_Open()
    With Application
        .Calculation = xlCalculationAutomatic
        .Iteration = True
        .MaxIterations = 100
        .MaxChange = 0.001
    End With
End Sub

Now save your file (if you are using Excel 2007 or up, make sure you change the file-type to one that can hold macro's, otherwise the macro code is discarded after you close your file!)


 


Comments

All comments about this page:


Comment by: Hans (9/12/2012 10:47:30 AM)

I try to do an iterative calculation in excel.
The goal is to get such a value in a certain cell (H8), that the result of the addition of two cells (B28+C28) becomes close to zero.
Formula =ALS((B28+C28)>0;(H8+0,001;(B28+C28)<0;H8-0,001))
An error message appears;
For this function you have entered too many arguments.
Indeed, to get the result in cell C28 a complicated calculation is used.
I can not use Goal Seeker or Solver, because the calculation has to be done in 88 rows at the same time.
Is there a solution for this problem?
Best regards, Hans

 


Comment by: Jan Karel Pieterse (9/13/2012 9:51:51 AM)

Hi Hans,

It is a bit hard to troubleshoot your formula without knowing what it needs to do. I guess something like this:

=ALS((B28+C28)>0;H8+0,001;ALS((B28+C28)<0;H8-0,001);H8)

 


Comment by: Michael (9/24/2012 11:22:36 AM)

Hi - I am really stuck. I have a deliberate circular re reference (a fee of 1% of funding balance - funding balance dependent on repayment of costs -which includes fee).

I have iterative calcualtions on, I hvae tried 100, 250, 1,000 iterations, with .00001 delta. Excel cannot solve, the change in its iterations quickly is off scale.

I can iterate manually within 20 goes -what is excel doing?

Your help will be much appreciated.

Thank you

M

 


Comment by: Jan Karel Pieterse (9/24/2012 11:40:51 AM)

Hi Michael,

It is not easy to answer your question without the model in question.

Not easy to explain, bare with me...

Sometimes it helps to calculate the difference between the input cell and the end result by splitting the cell with the end result/input into two cells; one "input" cell and one "Output" cell. You then use an additional cell to calculate the difference between input and output and use a fraction of the difference between input and output to correct the new input value. By playing around with that fraction you may be able to stabilise the model.

 


Comment by: Jashmin (2/1/2013 10:44:55 AM)

I am deliberately using circular reference for one calculation worksheet. I am getting #DIV/0! error in some of the cells utilized in circular reference. Though chnages made in the fields are no where used in the circular reference, it induces other cells to turn in to error #DIV/0!. Please help me out

 


Comment by: Jan Karel Pieterse (2/1/2013 3:07:38 PM)

Hi Jashmin,

I suggest you to read the next pages, where I explain how to cater for such situations.

 


Comment by: ss (3/31/2013 7:52:25 AM)

Hi,

Thanks its wk now

 


Comment by: Mark (5/6/2013 6:52:51 PM)

Not sure if Michael is still having his problem, but for anyone else perhaps Excel is having a problem with the fractions being used. I have read about how excel stores everything in binary numbers which can lead to rounding errors, depending on the fractions involved. It's mentioned in the Excel help.

 


Comment by: Guillermo Hoffman (11/13/2013 8:07:31 PM)

I have a list of 15000 items, i need a Macro that can take a value in a cell and find the approximate match with in a description and take me to that cell:
Cell Value B3= S6070 Range =
The descriptors are:A22:A15000
TMC - Smooth-Star - # S6070 C - Brushed Nickel     Texas Star 32 X 80     (2/8 - 6/8)
Cuurently I have these two macros running, but I have to enter the value in the MsgBox instead of having it read from the cell:

Sub COLUMN_CHOOSE()
'
' COLUMN_CHOOSE Macro
'

'
    Range("A19:A12047").Select
End Sub
Sub FindnGo()
    Dim MyString As String
    Dim c As Range
    
    MyString = InputBox("Enter word or part to search for")
     'select column to search
    With Selection
        Set c = .Find(MyString, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select

        
        End If
        
    End With
End Sub

(Also: if I enter a Letter in the MsgBox, the Macro does not Work?)
Appreciate any help-- Thank You
Gill

 


Comment by: Jan Karel Pieterse (11/14/2013 6:28:10 AM)

Hi Guillermo,

You can read the value from a cell like this:

MyString = Range("C1").Value

 


Comment by: Beka (11/19/2013 9:50:50 AM)

Dear Guillermo,

I am trying to apply the following condition:

IF(DI6697="";0;DI6697)

but I keep getting the wrong result, so even when the cell contains some value, for example 3, zero will be returned.

I am not sure where I am making a mistake?

Thank you.

 


Comment by: Jan Karel Pieterse (11/19/2013 11:03:41 AM)

Hi Beka,

Perhaps your calculation settings are wrong?

 


Comment by: Jeremy (11/19/2014 10:42:07 PM)

Thank you! Thank you! This problem was driving me crazy. When others opened my file and had other files already open, it (usually) gave the circular reference error even though my file is 'clean'. You provided an easy and effective solution to that problem. Thank you again!

 


Comment by: Sasha (2/26/2016 11:54:27 PM)

Hi JKP,

I am currently saving registry keys to allow toggling from one workbook to another (and thus modifying the application settings from the original settings to the specific ones) on Activate and Deactivate events. Is this a good route to go for in your opinion?

 


Comment by: Jan Karel Pieterse (2/27/2016 11:37:53 AM)

Hi Sasha,

I'd say if your solution works reliably and meets your business needs, then it is a good solution. As simple as that.

 


Comment by: Sasha (3/1/2016 1:48:29 AM)

Hi JKP,

Thanks for your input. Furthermore, I couldn't help but notice that that you are setting the calculation to "xlCalculationAutomatic". As I consider myself mostly a power coder, I fail to realise why this is necessary and would like to hear your thoughts on doing this instead of simply setting it to xlCalculationManual on startup and triggering the calculations on events when necessary with "Application.CalculateFull". Thank you again!

 


Comment by: Jan Karel Pieterse (3/1/2016 9:23:47 PM)

Hi Sasha,

I think because I expect that that will ensure Excel handles the iteration properly. I expect it doesn't make much of a difference, but I haven't tested this to be honest.

 


Comment by: Harry (3/29/2016 7:05:53 AM)

I am using Circular reference formula in one column "m" i.e =m7+f7. But any Changes made to the column 'f" in any other rows also automatically increases the value in the circular reference column "m". Any idea why?

 


Comment by: Jan Karel Pieterse (3/29/2016 9:30:45 AM)

Hi Harry,

This is because circular references are in fact volatile, meaning they will recalculate on every calculation.

 


Comment by: Jerry Hartis (5/2/2016 3:37:55 AM)

In working with a chemical plant with a very large recycle loop, if an error occurs any where in the calculation, it can be a large problem and very hard to fix.

To prevent the whole workbook going to #error, we have closed the circular calculation with a macro that sets the value of the first cell in the large loop with the value of the last cell. We run the macro a few iterations until the calculation converges. With this, Excel doesn't even detect the circular calculation and the whole spreadsheet does not turn to #error from a bad keystroke. Repeat as needed, if there are more circular calculations add them to the same macro.

 


Comment by: Jan Karel Pieterse (5/2/2016 6:46:43 AM)

Hi Jerry,

Thanks for your comments. This is precisely what I suggest on the next page:

http://www.jkp-ads.com/Articles/circularreferences03.asp

 


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