Forum Discussion
Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)
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.
