Forum Discussion
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
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.
Why FastExcel V4 - Decision Models
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!].
Possible solutions are here Cumulative Sum Formula using new Excel Dynamic Array Formulas - Stack Overflow
Example:
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)
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 !!
