Forum Discussion
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
in E3
=MAKEARRAY(
ROWS(TodayArray), COLUMNS(TodayArray),
LAMBDA(r,c,
SUM( INDEX(TodayArray,SEQUENCE(r),c) )
)
)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
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)) ) )=MAKEARRAY( ROWS(TodayArray), COLUMNS(TodayArray), LAMBDA(rw,cl, SUM( INDEX(TodayArray,rw,SEQUENCE(cl)) ) ) )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
- Brilliant. I'm going to get a lot of use out of that. Thank you
- Glad I could help & you have LAMBDA & Co. + Thanks for providing feedback
