Usually, circular calculations are a problem, but not always. Take this example: To compute your federal tax, you can deduct state taxes. To compute your state tax you start with federal taxable income. But wait, how do you know your federal taxable income until you’ve computed your state tax? How can you compute your state tax until you know your taxable income? Circular calculation to the rescue.
Here’s an example worksheet that illustrates the point. You can see that Excel has provided some blue arrows to tell me that I have a circular reference. (It also opens help for me – man, I hate that.)
To get rid of those errors and let Excel do the calculation for you, go to Tools>Options>Calculation and check the Iteration checkbox.
The default maximums are usually sufficient. Now Excel will iterate through the calculation until it reaches an answer or reaches its maximum iterations. The final calculation looks like this:
I also hate it when Excel automatically pops up the “Help” window describing circular references. This is very annoying, and I’m wondering if someone knows of how to disable this. I’m thinking it probably can’t be disabled, so is it possible to write VBA code that will initiate when help displays info on circular references? Could the code automatically cancel the help window when the circular references screen came up? I’m assuming that there may be a help ID number associated with the circular references help screen. Any ideas? Thanks,
Ron
Hitting the escape key avoids Help
Application.DisplayAlerts = False, not what your looking for i geuss Ron, buy you might be able to assign a keyboard short cut and toggle it on and off if your writing losts of cir refs.
On another not cir ref must be used carefully, esp. when dealing with tax, becase they make assumptions about when the tax (in this case) is companded – see the paper “Business Dynamics, Spreadsheet Modelling Best Practice” [4-31,32] for more info.
– doing a google with the qoute marks will bring up the papaer.
If you assign the macro to the Open event for the workbook, then it will run every time the workbook is opened, ensuring that you won’t see the warning you don’t want to see. The macro appears as follows:
Private Sub Workbook_Open()
Application.Iteration = True
End Sub
If you have a Personal.xls workbook defined for your system, you can add this macro to it instead of to individual workbooks. In that way you can ensure that the Iteration check box is always selected for every Excel session.
I have found that if you need to debug an iterative process, you can set the number of iterations to 1 in Tools>Options>Calculation and just step through it.
Isn’t there a way to leave iterations always on by putting something in the registry? Usually use Excel to make simulations of scientific problems and it is annoying to have to switch iteration on all the time. Trying to avoid macros I was hoping there would be a registry key overriding this behaviour and set iteration = always on. Anyone?
Thank you for this. It is now in Options-Formulas – Enable Iterative calculation