Forum Discussion
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.
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
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.
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)
- 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 !!
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.
This is a great solution!
Sorry to ask additional questions in an old thread but is there a solution where the Dynamic Array (spill) is included in the cumulative sum formula?
My spill formula is P4=INDEX(SORTBY(B4:C8,B4:B8,1),,2), where the cumulative formula is
=LET(s, P4#, k, SEQUENCE(ROWS(s)), SUBTOTAL(9,OFFSET(s,0,0,k)))
However, I would like to have a formula corresponding to
LET(s, INDEX(SORTBY(B4:C8,B4:B8,1),,2), k, SEQUENCE(ROWS(s)), SUBTOTAL(9,OFFSET(s,0,0,k)))
This does not work because the OFFSET() does not reference a cell - is there a way to solve this?
Thanks a lot!
