Forum Discussion

Copper Contributor

Feb 24, 2022

Solved

Cumulative Sum by column for two dimensional dynamic arrays?

Referencing a two dimensional dynamic array, how would I output a running total two dimensional array which calculates cumulative sums by column? Thanks in advance. Daniel

excel

Lorenzo

Feb 25, 2022

ds100

in E3

=MAKEARRAY(
    ROWS(TodayArray), COLUMNS(TodayArray),
    LAMBDA(r,c,
        SUM( INDEX(TodayArray,SEQUENCE(r),c) )
    )
)

Lorenzo

Silver Contributor

Feb 25, 2022

ds100

in E3

=MAKEARRAY(
    ROWS(TodayArray), COLUMNS(TodayArray),
    LAMBDA(r,c,
        SUM( INDEX(TodayArray,SEQUENCE(r),c) )
    )
)

Daniel2010

Copper Contributor

Oct 06, 2022

Lorenzo

Can you do it the other way around?

Today	Array	Running	Totals
1	2	1	3
2	4	2	6
3	6	3	9
4	8	4	12
5	10	5	15

The solution is awesome

Lorenzo

Silver Contributor

Oct 07, 2022

Alternative for Excel 2021 where MAKEARRAY isn't available

in F2:

=SUBTOTAL(9,
    OFFSET(B2,,
        SEQUENCE(,COLUMNS(B2:D6),0),
        SEQUENCE(ROWS(B2:D6))
    )
)

in J2:

=SUBTOTAL(9,
    OFFSET(B2,
        SEQUENCE(ROWS(B2:D6),,0),
        ,,
        SEQUENCE(,COLUMNS(B2:D6))
    )
)

DynamicRunningTotals.xlsx17 KB

Lorenzo
Silver Contributor
Oct 07, 2022
Daniel2010

=MAKEARRAY( ROWS(TodayArray), COLUMNS(TodayArray), LAMBDA(rw,cl, SUM( INDEX(TodayArray,rw,SEQUENCE(cl)) ) ) )
- Daniel2010
  Copper Contributor
  Oct 07, 2022
  Lorenzo
  Very elegant. I am using this cumulative sum formula in order to apply % increases to a value in a previous period (i.e. Quarter, Month, Year, etc...):
  
  % Increases per item
  Id P1 P2 P3
  A 2%, 3%, 5%
  B 2% 4% 2%
  C 1% 5% 3%
  Id P0 P1 P2 P3
  A 100% 102% 105% 110%
  B 100% 102% 106% 108%
  C 100% 101% 106% 109%
  
  I then just multiply the initial value by the resulting array and I get an array of values for each period with their cumulative % increase.
  
  Thank you much and regards