Reusing Array stored in single cell (despite #SPILL!)

Reusing Array stored in single cell (despite #SPILL!)



 Feb 08 2023
It'd be great if we could reuse the array stored within a single cell even if that array cannot "spill", i.e. generates a #SPILL! error.
You can test this very simply as follows:
Cell A1:  =SEQUENCE(5,1,1,1)
Cell B1: =A1#+1
Cell C1: =AVERAGE(B1#)
If you force the #SPILL! by putting something into one of the lower cells (e.g. A2), then the following functions fail as well with the #SPILL! error.
While nesting the formulas would work, it's not too practical if sub-sections of the calculation are to be analyzed or reused elsewhere - in this case we'd need to recalculate the same things multiple times.
Such a functionality could be helpful esp. in applications where (discrete) probability distrubtions are used (e.g. monte carlo) in multiple parts of the calculation where having the full array spilled out is not required or practical.
While this certainly would be possible using VBA/Office Script, having it implemented in standard formulas is quite attractive from a user-experience POV.