Apr 29 2021 10:21 PM
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!
Apr 30 2021 12:43 AM - edited Apr 30 2021 12:51 AM
You may try this :
=SUM(INDEX(Mydata,1,1):INDEX(Mydata,ROW()-ROW(INDEX(Mydata,1,1))+1))
N.B. Replace Mydata (is Named Range) with the NAME you have given to the RANGE.
Apr 30 2021 05:39 AM - edited Apr 30 2021 05:54 AM
Possible solutions are here Cumulative Sum Formula using new Excel Dynamic Array Formulas - Stack Overflow
Example:
Apr 30 2021 05:55 AM
Apr 30 2021 08:44 AM
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)
Apr 30 2021 09:58 AM
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.
Apr 30 2021 10:07 AM
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))
))
Apr 30 2021 10:33 AM
LET() shall be in production, perhaps except deferred semi-annual channel, not sure. MMULT() works fine, but that's not exactly the spill.
Apr 30 2021 10:53 AM
You could rewrite @SergeiBaklan formula.
=SUBTOTAL(9,OFFSET(B2#,0,0,SEQUENCE(ROWS(B2#))))
Apr 30 2021 10:28 PM
May 01 2021 12:07 AM - edited May 01 2021 12:21 AM
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!].
Jul 26 2021 06:15 AM
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
Jul 26 2021 02:33 PM
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)
Oct 22 2021 11:51 AM
Great solutions guys, these work perfectly. Just starting to really try and get everything to be dynamic to avoid manual updating.
Oct 22 2021 01:04 PM
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.
Oct 22 2021 03:28 PM
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.
Oct 22 2021 03:43 PM
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
Oct 23 2021 08:20 AM
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.
Nov 23 2021 01:51 AM
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)
Nov 23 2021 02:53 AM
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.