Is this the best way to generate a cumulative column in a table?

[JenniferMurphy]

I need a cumulative column in a table that will survive adding, deleting, moving, and sorting rows. This is what I came up with. Is it the best way? It works, but it seems inefficient.

Typing Tutor Adaptive Learning Algorithm.xlsm
	I	J
5	Prob	Cum Prob
6	10.00%	10.00%
7	20.00%	30.00%
8	30.00%	60.00%
9	40.00%	100.00%
10	100.00%
Weighted Drills 03

Cell Formulas
Range	Formula
J6:J9	J6	=SUM(OFFSET(Table10[[#Headers],[Prob]],1,0):Table10[@Prob])
I10	I10	=SUBTOTAL(109,[Prob])

 

[fjns]

Hi, see the linked file for a possible solution...

The formulas used in the table:
A1: =IF(INDEX(Work!I:I,ROW())="","",INDEX(Work!I:I,ROW()))
B2: =IF(A2="","",SUM(A$2:A2))
C2: =SUM(A:A)

Sum.xlsx

 

[Fluff]

I tend to use

Fluff.xlsm
	I	J
5	Prob	Cum Prob
6	10%	10%
7	20%	30%
8	30%	60%
9	40%	100%
Main

Cell Formulas
Range	Formula
J6:J9	J6	=SUM(Table2[[#Headers],[Prob]]:[@Prob])

 

[JenniferMurphy]

I tend to use . . .

That's much better. So the syntax =TableName[[#Headers],[ColumnName]] means "the top of the column" or "the first data member"?

Very nice and much cleaner than my method. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Dave Patton]

Another option to consider

T202302.xlsm
	A	B	C
1	Prob	Cum Prob	Or
2	10%	10%	10%
3	20%	30%	30%
4	40%	70%	70%
5
2c

Cell Formulas
Range	Formula
B2:B4	B2	=SUM(Table2[[#Headers],[Prob]]:[@Prob])
C2:C4	C2	=SUM(INDEX([Prob],1):[@Prob])

 

[JenniferMurphy]

Another option to consider . . .

Great, very helpful.

Here's a little sheet with a table showing the various options.

Tables.xlsx
	B	C	D	E	F	G	H	I
4	Item	Weight	Prob	Cum Prob Me	Cum Prob Fluff	Header	Cum Prob Patton	Prob #1
5	A	6	23.08%	23.077%	23.077%	Prob	23.08%	23.08%
6	B	2	7.69%	30.769%	30.769%	Prob	30.77%	23.08%
7	C	3	11.54%	42.308%	42.308%	Prob	42.31%	23.08%
8	D	1	3.85%	46.154%	46.154%	Prob	46.15%	23.08%
9	E	7	26.92%	73.077%	73.077%	Prob	73.08%	23.08%
10	F	4	15.38%	88.462%	88.462%	Prob	88.46%	23.08%
11	G	3	11.54%	100.000%	100.000%	Prob	100.00%	23.08%
12		26	100.00%
Cum Columns

Cell Formulas
Range	Formula
D5:D11	D5	=[@Weight]/TblCum[[#Totals],[Weight]]
E5:E11	E5	=SUM(OFFSET(TblCum[[#Headers],[Prob]],1,0):[@Prob])
F5:F11	F5	=SUM(TblCum[[#Headers],[Prob]]:[@Prob])
G5:G11	G5	=TblCum[[#Headers],[Prob]]
H5:H11	H5	=SUM(INDEX([Prob],1):[@Prob])
I5:I11	I5	=INDEX([Prob],1)
C12	C12	=SUBTOTAL(109,[Weight])
D12	D12	=SUBTOTAL(109,[Prob])

And here is the same table sorted on the Weight column to illustrate that the cumulative columns still work properly.

Tables.xlsx
	B	C	D	E	F	G	H	I
4	Item	Weight	Prob	Cum Prob Me	Cum Prob Fluff	Header	Cum Prob Patton	Prob #1
5	E	7	26.92%	26.923%	26.923%	Prob	26.92%	26.92%
6	A	6	23.08%	50.000%	50.000%	Prob	50.00%	26.92%
7	F	4	15.38%	65.385%	65.385%	Prob	65.38%	26.92%
8	G	3	11.54%	76.923%	76.923%	Prob	76.92%	26.92%
9	C	3	11.54%	88.462%	88.462%	Prob	88.46%	26.92%
10	B	2	7.69%	96.154%	96.154%	Prob	96.15%	26.92%
11	D	1	3.85%	100.000%	100.000%	Prob	100.00%	26.92%
12		26	100.00%
Cum Columns

Cell Formulas
Range	Formula
D5:D11	D5	=[@Weight]/TblCum[[#Totals],[Weight]]
E5:E11	E5	=SUM(OFFSET(TblCum[[#Headers],[Prob]],1,0):[@Prob])
F5:F11	F5	=SUM(TblCum[[#Headers],[Prob]]:[@Prob])
G5:G11	G5	=TblCum[[#Headers],[Prob]]
H5:H11	H5	=SUM(INDEX([Prob],1):[@Prob])
I5:I11	I5	=INDEX([Prob],1)
C12	C12	=SUBTOTAL(109,[Weight])
D12	D12	=SUBTOTAL(109,[Prob])