Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Overlapping Circular References (2003 (11.8146.8132) SP2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Overlapping Circular References (2003 (11.8146.8132) SP2)

    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.

  3. #3
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overlapping Circular References (2003 (11.8146

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Overlapping Circular References (2003 (11.8146

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •