Dynamic Table: Progressive Row SUM

[epardo87]

Hello there,
I have a table where each row is a different Date (Event) and each column contains different statistical data, what I want to do is to create a dynamic chart where each row adds up the data of the previous events, some columns can be a simple sum and others complex formulas, I can manage that, but what I haven't figure out is how to make each row grow... I leave this simple example here, I know how to do this with a simple spreadsheet and formulas for each cell but since the actual tables are huge, I would like to know if there is a way to do it through dynamic tables

So this is and idea of what I have, each row contains stats of that specific event

Date	Stat A	Stat B	Stat C (=A/B)
1/1	5	10	0.50
1/2	4	20	0.20
1/3	6	15	0.40

What I expect to have is a table of progressive added up stats:

Date	Progressive Stat A	Progressive Stat B	Progressive Stat C (=A/B)
1/1	5	10	0.50
1/2	9	30	0.30
1/3	15	45	0.33

Thanks in advance for your time and advice

Regards!

 

[JamesCanale]

This seems like it got more complicated than it should have. Assuming you have 365 (from your question wording)...

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Stat A	Stat B	Stat C (=A/B)				Date	Progressive A	Progressive B	Progressive C
2	1-Jan	5	10	0.5				1/1/2022	5	10	0.50
3	2-Jan	4	20	0.2				1/2/2022	9	30	0.30
4	3-Jan	6	15	0.4				1/3/2022	15	45	0.33
5	4-Jan	2	2	1				1/4/2022	17	47	0.36
Sheet22

Cell Formulas
Range	Formula
H2:H5	H2	=Table2[Date]
I2:I5	I2	=LET(a,Table2[Stat A],b,SEQUENCE(1,ROWS(a),1,0),c,MMULT(a,b),d,SEQUENCE(ROWS(c),COLUMNS(c)),e,MOD(d-1,COLUMNS(d))+1,f,INT((d-1)/COLUMNS(d))+1,g,IF(e>=f,c,0),h,SEQUENCE(ROWS(d),1,1,0),MMULT(TRANSPOSE(g),h))
J2:J5	J2	=LET(a,Table2[Stat B],b,SEQUENCE(1,ROWS(a),1,0),c,MMULT(a,b),d,SEQUENCE(ROWS(c),COLUMNS(c)),e,MOD(d-1,COLUMNS(d))+1,f,INT((d-1)/COLUMNS(d))+1,g,IF(e>=f,c,0),h,SEQUENCE(ROWS(d),1,1,0),MMULT(TRANSPOSE(g),h))
K2:K5	K2	=I2#/J2#
D5	D5	=[@[Stat A]]/[@[Stat B]]
Dynamic array formulas.

 

[epardo87]

This seems like it got more complicated than it should have. Assuming you have 365 (from your question wording)...

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Stat A	Stat B	Stat C (=A/B)				Date	Progressive A	Progressive B	Progressive C
2	1-Jan	5	10	0.5				1/1/2022	5	10	0.50
3	2-Jan	4	20	0.2				1/2/2022	9	30	0.30
4	3-Jan	6	15	0.4				1/3/2022	15	45	0.33
5	4-Jan	2	2	1				1/4/2022	17	47	0.36
Sheet22

Cell Formulas
Range	Formula
H2:H5	H2	=Table2[Date]
I2:I5	I2	=LET(a,Table2[Stat A],b,SEQUENCE(1,ROWS(a),1,0),c,MMULT(a,b),d,SEQUENCE(ROWS(c),COLUMNS(c)),e,MOD(d-1,COLUMNS(d))+1,f,INT((d-1)/COLUMNS(d))+1,g,IF(e>=f,c,0),h,SEQUENCE(ROWS(d),1,1,0),MMULT(TRANSPOSE(g),h))
J2:J5	J2	=LET(a,Table2[Stat B],b,SEQUENCE(1,ROWS(a),1,0),c,MMULT(a,b),d,SEQUENCE(ROWS(c),COLUMNS(c)),e,MOD(d-1,COLUMNS(d))+1,f,INT((d-1)/COLUMNS(d))+1,g,IF(e>=f,c,0),h,SEQUENCE(ROWS(d),1,1,0),MMULT(TRANSPOSE(g),h))
K2:K5	K2	=I2#/J2#
D5	D5	=[@[Stat A]]/[@[Stat B]]
Dynamic array formulas.

Thanks for your answer, I'm still trying to make your formulas work but haven't succeed, but before going further I have one question: I'm sorry for not bringing this up in the first place, but there is one other column I didn't mentioned for the sake of making the example simple, but each event (row) involves a different subject (from a limited group of subjects), if I filter this column per subject, will these formulas show the progressive calculation of that single subject?
Do you think this can be solved through a Pivot table?

Date

Subject

Stat A

Stat B

Stat C

 

[JamesCanale]

These formulas won't change if you change the filter. If you want that, then where it says
Table2[Stat A]
you'd have to wrap a FILTER around it. So FILTER(Table2[Stat A],Table2[otherfiltercolumn]=??)
One trick I often do, is to put a slicer on the table with that filter column that you are interested in, and then make another column with an AGGREGATE count or counta function that will ignore hidden rows and set it to look at a cell in its own row. Then filter out the 0's in the new aggregate column. So when you run the slicer, the hidden rows all get filtered out of your results. So something like a pivot table without the refreshing.
As far as unpacking the formulas in my LET - those MMULTs aren't intuitive. I'd look at each of the variables (a,b,c,...) to get a feel on how it's working. Mostly, I'm trying to create a 2D array that looks like 'g' in my let.