 # 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!

12 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 @Sergei Baklan 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 @Sergei Baklan 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-S

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)