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.
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.
