Overlapping Circular References (2003 (11.8146.8132) SP2)

Topic

New Reply

I have a workbook with some intentional circular references. Howver, some cells are involved in more than one circularity. I find that each time I calculate me spreadsheet, I get a different result for some of the circular reference results. Does Excel support overlapping circular references? Can anyone point me to some good reading material on this?

Thanks
Vyyk

Reply | Quote

Replies

Welcome to Woody’s Lounge!

If you use circular references, you must limit the number of recalculations by setting the maximum number of iterations and/or the maximum change in the Calculation tab of Tools | Options…
But if one cell is involved in multiple circular references, you don’t know in which order the iterations are performed, so the result becomes unpredictable.
I haven’t been able to find a tutorial on this subject – I suspect that most people avoid using multiple circular references.

If you explain what you want to accomplish, someone may be able to suggest an alternative approach.

Reply | Quote

Well, this won’t clear anything up but thought I’d mention it.

I work at a bank. Almost all our bankers use a set of circular references for interest calculations which have many cells that are involved in multiple circles.

When implemented properly, in files without too much corruption, they work consistently. But then, every once in a while, we’ll get a file where they aren’t stable. Most often it involves one cell directly linked to another cell (e.g., cell A1 has =B1 in it), where the two cells trade off values at each calculation, so A1 will = 1153 and B1 will = -94, then after the next calc, A1 will equal 94 and B1 will equal 1153.

With these files, it always turns out that the banker has deviated (usually inadvertantly) in some way from the standard set-up.

I guess certain types of circles with shared cells would have to be unstable. In the simplest form, a cell that was the center cell in a figure 8 would be problematic, because Excel would have to calc one of those two circles last. But unfortunately, I’ve never had the time to examine them closely and see which sorts of shared cells are ‘safe’. I’d be very interested to hear anyone else’s thoughts on this subject.

Best

Reply | Quote

A couple remarks. It is important in these schemes (as well as many things) to have adequate training and education to ensure that deviations from the guidelines and procedures are not done.

If at all possible, schemes should be developed which do not use circular references. More direct solutions could be used, but if iterative calculations are the best solution, they may be able to bemade and developed to not use circular references at all.

Steve

Reply | Quote