6

I'm using the new Dynamic (ie Spill) formulas in Excel. I want a dynamic array that is the accumulation or running total of another dynamic array.

Let's assume I have the following dynamic data in A1:A8:

12
20
14
13
12
13
26
11

To find the differences in this array is trivial:

=OFFSET(A1#,1,0)-A1#

8
-6
-1
-1
1
13
-15
-11

But how do I get the running total using the new dynamic formulas?

12
32
46
59
71
84
110
121
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
kale
  • 191
  • 2
  • 8

6 Answers6

8

Here's another approach using Matrix Multiplication

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),SIGN(I3#))
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
7

New Answer 2022-06-20

For a dynamic array in A1 use:

=MMULT(N(ROW(A1#)>=TRANSPOSE(ROW(A1#))),A1#)

If the dynamic array in A1 has multiple columns, this returns the cumulative sum of each column, unlike my more complicated Original Answer.

Original Answer 2022-01-13

By using SIGN(I3#), chris neilsen's solution accumulates absolute values. To accommodate negative numbers, replace SIGN(I3#) with 1*(I3#=I3#) in your cumulative sum:

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),1*(I3#=I3#))

Alternatively, generate a dynamic array of ones with

SEQUENCE(ROWS(I3#),,,0) instead of 1*(I3#=I3#).

(I lack the reputation to comment.)

bkraines
  • 83
  • 1
  • 4
  • The following formula is just a little bit easier in may eyes, but still basically the same: `MMULT((ROW(D2#)>=TRANSPOSE(ROW(D2#)))*1,D2#)`; by changing `<=` to `>=`, you can save a `TRANSPOSE`. – Lukas Aug 04 '23 at 09:38
7

Cumulative Sum Formula, using SCAN() & LAMBDA() -- Dynamic Spill Array Formulas

FORMULA_SOLUTION

• Formula used in cell B1 --> Approach using SCAN() & LAMBDA() --> Applicable To MS365

=SCAN(0,A1:A8,LAMBDA(x,y,x+y))

• Formula used in cell C1 --> Approach using VSTACK(), SCAN() & LAMBDA() --> Applicable to MS365 Office Insiders Beta Channel

=VSTACK(A1,SCAN(A1,A2:A8,LAMBDA(x,y,x+y)))

• Formula used in cell D1 --> Approach using LET(), SCAN() & LAMBDA() --> Applicable to MS365

=LET(x,A1:A8,
sum,LAMBDA(z,y,z+y),
SCAN(0,x,sum))

Using MAP() & LAMBDA()

FORMULA_SOLUTION

• Formula used in cell B1

=MAP(A1:A8,LAMBDA(x,SUM(A1:x)))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
2

Here's one way I've done it, but wondering if there's an easier/more efficient way to do it...

=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(COUNT(A1#))))
kale
  • 191
  • 2
  • 8
1

This one solves a rolling twelve sum

=SUBTOTAL(9,OFFSET(A1#,SEQUENCE(COUNTA(A1#),1,0),0,12))
Eduardo Nozawa
  • 11
  • 1
0

You can also use the following:

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,SEQUENCE(NumberOfRows,0,0,1),0))))

Assume in the above that cell A1 contains the spill formula for the values you want to sum. You can also use a helper column for SEQUENCE(), and if you did that in column B you would use:

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,B1#,0))))

THIS ALSO WORKS FOR A CUMULATIVE PRODUCT, IF YOU USE =SUBTOTAL(6,...)

I found that the answers given above didn't work for me.

  • For the matrix multiplication, it works for cumulative sums but it doesn't work for cumulative products. So my answer is a bit more generalised.
  • For the earlier answers using SUBTOTAL(), for some reason I had to add a number to the values generated by SEQUENCE(). In my case, I had to add 3 which just seemed random. I could find no reason for having to add it, but without it the cumulative sum was out by 3 rows.

My answer doesn't seem to work for the PRODUCT() and SUM() formulae, only for SUBTOTAL(). I'd guess that PRODUCT() and SUM() haven't yet been changed to spill/array type formulae, but SUBTOTAL has.

Punter
  • 13
  • 4