Forum Discussion
Solved
Spills and the calculation tree
Just hit upon my first disappointment with (the otherwise fantastic) Spills. Not sure how to efficiently communicate this, so forgive long-hand:
A1=1
B2=A1
Copy down to B10 (=A9)
A2=A1-B2+1
Copy down to A10 (=A9-B10+1)
Press Calc. All good.
Now replace A2 with =A1-B2:B10+1 - get #SPILL!
Clear the space below. Calc.
We get a Circular Reference.
As the equivalent line-by-line example showed, the algorithm isn't strictly circular. But it seems that a refresh of a single cell inside a Spill taints the entire Spill.
Unfortunate.
Dynamic array formula doesn't calculate cell by cell, it calculates entire array and returns result into the range. With this formula it gets the range B2:B10 into array, starts calculating it, but second element of the array refers on the cell with formula. Thus in status bar you have a notice that circular reference is in A2.
Bit more about behaviour
Dynamic array formulas vs. legacy CSE array formulas (microsoft.com)
Dynamic array formula doesn't calculate cell by cell, it calculates entire array and returns result into the range. With this formula it gets the range B2:B10 into array, starts calculating it, but second element of the array refers on the cell with formula. Thus in status bar you have a notice that circular reference is in A2.
Bit more about behaviour
Dynamic array formulas vs. legacy CSE array formulas (microsoft.com)
Thanks, Sergei, this confirms my observation. I think this needs to be borne in mind when working with (large) Spills because it has performance implications. It probably does not cause dependency issues so long as one works exclusively with spilled vectors. The problem arises on the boundary between cell and vector calculations. This may be a very esoteric application because the reader might puzzle why I would introduce a variable vector into a fixed cell calculation grid. But I actually used ISBLANK within the cell grid to learn something about the vector. It badly backfired.
