BUG in excel iteration system? Very simple illustrative example: can you replicate?

[cxc]

Hello, I am currently running Excel 2003 SP2, and I am running into a
very strange issue:
1) open a new spreadsheet
2) in tool/options/calculation: enable iteration and set max iteration
to "1" (leave calculation on automatic)
3) we will do something very simple: increment a cell on each
iteration. To do this, simply go into cell A2 and type: "=a2+1". To
see the cell getting incremented, simply press F9 for each iteration.
4) I now want cell A1 and A3 to each display the value that is in A2:
to do this I go in cell A1 and type "=a2", and then go into cell A3
and type "=a2"
5) press F9 several times to increment the cell A2

=> Now it seems to me that A1 and A3 should both display what is in
cell A2...well on my spreadsheet A3 displays it alright, but A1 is 1
iteration behind!!!
=> The first thing I'd like help on is whether or not anyone else can
replicate the same issue using my example, and the second thing is: is
there a workaround?

Thanks in advance for the help!

 

[Doug Glancy]

cxc,

I've never used iteration, but I assume that the calculation starts in A1
and moves through the sheet. So, when A1 is calculated A2 hasn't been
incremented yet. Then A2 is incremented, but since you've limited the
iterations, the calculation never gets back to A1. A3, being after A2 in
the calculation order is the same as A2 when the iteration/calculation is
done.

I'm not sure that's what's happening, but I imagine it is.

The workaround might be to only have the dependent cells "after" the cell
they depend on.

hth,

Doug

 

[cxc]

Doug -

Yes that's what I figured, but it seems non-sensical to have cells
return different values depending on their position in the
spreadsheet. This is a simple example but imagine a large spreadsheet
with multiple circular references all over the place: you can't just
ask users to move hundreds of cells around just to follow a particular
sequential order from top to bottom, it's completely unmanageable (in
theory the circular references should be updated first, then the rest
of the spreadsheet).

Very strange programming from the excel team on this one...I wonder if
there is a patch to address that...

 

[Doug Glancy]

cxc,

"imagine a large spreadsheet with multiple circular references all over the
place"

I'd rather not <g>.

Doug