# Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Copper Contributor

# Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Hello Excel Community,

I have a simple row of numbers with a range name called Numbers

I'm looking for a spill formula (one that returns a dynamic array) that returns a cumulative sum.

I understand there are options for the non-spillable ways to generate the **bleep** such as =sum(\$a\$1:a2) and drag this formula across the row.

Thank you!

27 Replies

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

You may try this :

``=SUM(INDEX(Mydata,1,1):INDEX(Mydata,ROW()-ROW(INDEX(Mydata,1,1))+1))``

• Formula in cell S86, fill it down:

N.B. Replace Mydata (is Named Range) with the NAME you have given to the RANGE.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Picture disappeared. Repeat

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Hello,

Thanks for looking at my post. But unfortunately, this formula does not spill.

Making it spillable is what makes this particular formula exercise tricky :(

I'm not sure if there is a good way to do this without using Lambda (which I currently do not have an access for)

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

It is spill, please check attached. If you change spill in B2 formula follows it.

If you may do something without lamdas, do it. Lambdas in Excel are at very beginning, if you do something using them now, in few months most probably it could be done much easier. Also with lambdas. IMHO, using lambdas now is only to train the brains and be prepared to future Excel.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Hello Sergei,

I was replying to another commenter (his solution was using Sum(index... :index) approach which does not spill.

Yours look like it would spill as you pointed out.

I do not have an access to the latest O365 update with Let and Lambda features (I'm on an enterprise license and probably won't have those features until much later); I tried your file but it gives me #name error because my Excel doesn't know what Let() is.

So far, i have found this approach; it is rather a lengthy formula but it works.

=TRANSPOSE(MMULT(
(COLUMN(MyRange) <= TRANSPOSE(COLUMN(MyRange)))*MyRange,
TRANSPOSE(SIGN(MyRange))
))

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

LET() shall be in production, perhaps except deferred semi-annual channel, not sure. MMULT() works fine, but that's not exactly the spill.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

You could rewrite @SergeiBaklan formula.

``=SUBTOTAL(9,OFFSET(B2#,0,0,SEQUENCE(ROWS(B2#))))``

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Please share some sample data along with the expected output,,, otherwise what I've suggested & by @SergeiBaklan are working for Cumulative Sum as you have mentioned with the post !!

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

At present, I do not believe there is any completely satisfactory way of performing a simple balance calculation from a flow array using dynamic array formulas.  Currently this represents the most significant roadblock that prevents Excel models becoming dynamic in their entirety.

Computationally, the most efficient solution is to buy @Charles Williams's Fast Excel add-in that has a function

= ACCUMULATE(array, startValue, factors, open)

where factors allow for interest based on a prior balance or growth calculations.

The obvious calculation of adding the array to an offset copy of itself, works for CSE array formulas but not DA because DA does not support array breakup [CSE arrays flow just about as well as set concrete].

Other methods, such as using MMULT with an upper triangular matrix of 1s, or SUMIFS in which flow variables are added from earlier periods are O(n²) and eventually become computationally unaffordable.  The latter are, however, effective for small to medium-sized arrays [20,000 terms would start to be a problem].

Even with Lambda functions one does not entirely escape problems.  At the simplest level, it can be used as a 'wrapper' to make MMULT more acceptable to a non-mathematical user but that does not change the efficiency issue.

Recursion overcomes the problem of an array formula not being able to reference earlier terms of itself without generating a circular reference error.  Since every level of a recursion stack contains an image of every variable, care needs to be taken not to pass large arrays.  There are currently quite severe limitations on the depth allowed for recursion which require careful management [techniques involving passing data as functions to be evaluated later, rather than as arrays, or traversing the problem within a bisection tree rather than a list - not for the faint-hearted!].

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

@Rajesh_Sinha

Just read your proposed solution to this topic and it works perfectly for me in a worksheet (i.e. using columns & rows). The issue however is that I would like to do similar in a set of named arrays that do not have any associated rows, columns or cells.

I'm trying to implement the Adler-32 Checksum Algorithm (https://en.wikipedia.org/wiki/Adler-32) in Excel but without using any VBA. That works perfectly using cell ranges but not using a set of named arrays.

 B C D E 2 ASCII A32-A A32-B 3 String: 87 88 88 4 Wikipedia 105 193 281 5 107 300 581 6 Adler-32 Hex: 105 405 986 7 11E60398 112 517 1503 8 101 618 2121 9 Adler-32 Base64: 100 718 2839 10 EeYDmA 105 823 3662 11 97 920 4582

Formula =CODE(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)) in C2 converts the string "Wikipedia" into and array of ASCII characters (range C3:C11 is named ASCII) so array formula {=SUM(INDEX(ASCII,1):INDEX(ASCII,ROW()-ROW(INDEX(ASCII,1))+1))+1} in D2 computes the A part of Adles-32 and {=SUM(INDEX(Adler32A,1):INDEX(Adler32A,ROW()-ROW(INDEX(Adler32A,1))+1))+0} takes care of the B Part.

Formula =DEC2HEX(BITLSHIFT(MAX(E:E),16)+MAX(D:D),8) in B7 the calculates the final Adler-32 checksum.

I would like the arrays ASCII, Alder32A and Adlre32B to only exist as named (Name Manager) arrays but cannot figure out how to then change the array formulas that are now in cols D & E

Can you perhaps shed some light on that?

Thx

# Re: 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)

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

@Sergei Baklan

Great solutions guys, these work perfectly. Just starting to really try and get everything to be dynamic to avoid manual updating.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

Thank you, but idea of the solution was not new. And, as @Peter Bartholomew  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.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

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.

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

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

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

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.

# Re: 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)

# Re: Looking for Cumulative Sum Formula that returns Dynamic Arrays (i.e. spill-able)

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.