Help needed with Circular References

[Yogi Watcher]

Hi,

I am developing a Excel based solution and that requires
circular reference in the formulas. However I am ok with
setting the limit that each cell be calculated only once.
My question is what the order of recalculating cells is.
Is it row first starting from A1 or column first starting
from A1 or is it the order of entering the cell formulas?

Depending upon the order in which the cells are
calculated results can change. For example, if the
formulas in the cells A1 to A5 are as follows (value in
the bracket indicates initial value when formula is
entered):

A1: A5+1 (1)
A2: A1+1 (2)
A3: A2+1 (3)
A4: A3+1 (4)
A5: A4+1 (5)

If cells are calculated in order of A1-A2-A3-A4-A5 then
each time I press F9 cell values of A1-A2-A3-A4-A5 would
be 6-7-8-9-10, 11-12-13-14-15 and so on.

However if cells are calculated in order of A3-A4-A5-A1-
A2 then each time I press F9 cell values of A1-A2-A3-A4-
A5 would be 6-7-3-4-5, 11-12-8-9-10 and so on.

So the resultant value of each cell depends on order of
cell calculation. Is there any specific sequence in which
these cells are calculated?

The example I gave above is simple, but the solution that
I am developing has circular references spanning across 2
worksheets and lots of cells spread all over those
worksheets.

Thanks
Yogi Watcher

 

[JE McGimpsey]

Since you have solutions on different sheets, the recalculation method
will be different for XL2002/3 vs. previous versions. Here's an
explanation of XL's recalc process:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k
2/html/odc_xlrecalc.asp

 

[Yogi Watcher]

This is very good information, however that article does
not mention anything about recalculation process if there
are circular references in the formulas.

I am particularly interested in recalculation process
(ordering cells for recalculation and performing actual
recalculation) when there are circular references becuase
my solution involves circular references.

If there are no circular references then recalculation
order is important only for optimization not for results
(results will alway be correct). It is when there are
circular references order of recalculation becomes
important for optimization and for results.

What exception does Excel performs internally if it
encounters circular references?

 

[JE McGimpsey]

Sorry - don't think I've ever seen a more complete explanation of how XL
builds the calculation table. The answer will certainly depend, at least
initially, on what order the CRs are entered. However, that dependency
tree can be rebuilt in XL02/03 using CTRL-ALT-SHIFT-F9. After doing
that, you could test to determine the order.