Forum Discussion
Tables are fantastic but cumulative totals are a pain
I guess this article is one that will just run and run despite its original date!
A solution I adopted was to define a named reference 'prior' that refers to
=Sheet1!R[-1]
in R1C1 notation (i.e. the row above).
The accumulation may then be expressed using Range intersection
= [@amount] + IFERROR(prior [balance], 0)
where the error traps the NULL reference that results from applying the formula to the first row.
To accumulate over a filtered set, one could introduce a helper column 'selected?' to indicate whether the row is filtered or not (using a form of COUNT)
=SUBTOTAL(103,[@amount])
The accumulation then becomes
= IF([selected?], [@amount], 0) + IFERROR(prior [balance], 0)
There remains the problem that a Table is designed to hold an unsorted list whereas accumulation is essentially an array operation (integration) and requires the terms to appear in the correct order. To 'harden' the calculation in the presence of Sort is another challenge since 'prior' then has to pick out the record that immediately precedes the current record in terms of date, not position.
PeterBartholomew1 , I didn't catch why do you need helper column. IMHO,
= SUBTOTAL(4,[@amount]) + IFERROR(prior [balance], 0)
shall work (or 9, 109, etc)
Agreed. It was just the way I developed the solution. I should have reduced it to one of your suggested formulas rather just than settling on the first solution that worked. :-(