Forum Discussion
Tables are fantastic but cumulative totals are a pain
Excel Tables have too many benefits to list here, if you've never used them then check out my article here
Tables = Ctrl +T
One frustration I do have though is that cumulative totals...
Fair point @Ingeborg. An alternative for bigger data sets that can handle inserted / deleted rows would be this: I selected cell A2 and then defined the name "RowAbove" as = N(A1) Then my cumulative formula in the Table can be set as RowAbove + [@Units Sold]
You can also do the same thing with an old-style CSE array formula. By defining an offset range 'PriorBalance' to do the job of your relative reference 'RowAbove', one can use formulas such
= IF( Initialise?, CarriedForward, PriorBalance ) + Credit
for accumulation. It is robust to any drag-and-drop that may be used to adjust the credit amounts and no one is going to insert a row through it! Such formulas are out of spec for a modern dynamic arrays though. Neither exist within the Table though.
p.s. 'Initialise?' simply holds the first row test
= PriorBalance ="Balance"
