Forum Discussion
Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)
I know this has been answered now
But random googling on my part threw up this post
This one definitely works (and is simpler than some others here)
I thought of matrix multiplication as a hard sell to the financial modelling community for their corkscrew calculations. Something I felt offered a better chance was the conditional sum
= SUMIFS(newStaff, period, "<="&period)Now I just use
= SCAN(0, newStaff,
LAMBDA(acc,n, acc+n)
)and concepts like relative referencing and direct cell notations are just a fading memory.
Not that it helps when I try to communicate with spreadsheet users from the 'real' world.
Agreed. To make this work, I either had to use CSE, which supports array decomposition but is not dynamic, or enable circular referencing.
Thanks for sharing. Sadly I get a circular reference with 365 v2305 b16501.20196
on which version & build are you please?
I was facing the same issue and tried the following approach - fully expecting that it will complain about circular reference. Amazingly it did not! In B4 I entered a formula that contains B4 (via A4:C4 reference), yet, Excel recognized that there is no circular reference and did the cumulative calculation via spill. Pretty amazing.
I don't like to use OFFSET function because it is volatile, but if that is not a concern, formula can be written with appropriate named ranges as follows: =OFFSET(cum_range,,-1) + incr_range
