Forum Discussion
Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)
Maybe the title "Looking for Cumulative Sum Formula" should now be "Waiting for Cumulative Sum Formula". A certain amount was possible before
Ways of performing Accumulation with Dynamic Arrays - Page 2 - Microsoft Tech Community
but it seems that a definitive set of functions are emerging
Announcing LAMBDA Helper Functions (microsoft.com)
Great solutions guys, these work perfectly. Just starting to really try and get everything to be dynamic to avoid manual updating.
Just in case this discussion comes to light in future, the formula using Lambda helper function can be expressed as
= LET(seq, B2#, SUMλ, LAMBDA(t,s, t+s), SCAN(0,seq,SUMλ))I just hope that the optional initialisation parameter '0', and the scanned array 'seq' will have changed places by the time the helper functions come out of beta release.
Usually in SCAN first zero is only for lifting,
= LET(seq, +B2#, SUMλ, LAMBDA(t,s, t+s), SCAN(,seq,SUMλ))shall work as well
True. I have mixed feelings about optional parameters that are followed by further parameters with values. I often insert the value I know to be the default simply to create a uniform pattern of parameters.
The formula that remains something of a mystery to me is your earlier formula involving SUBTOTAL and OFFSET. It works with these specific functions but not any equivalent using SUM or INDEX. Using F9 shows an array of #VALUES! for the nested sub-arrays before it magically corrects itself and gives the accumulated values. It is still an O(n²) formula rather that O(n) that is needed for accumulation. Even so, it is remarkable.
Thank you, but idea of the solution was not new. And, as PeterBartholomew1 mentioned, we have more and more options to do the same. With dynamic arrays, data types, etc Excel becomes significantly richer and more is coming. Great product.
