Forum Discussion
Spills and the calculation tree
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.
