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.
Thanks a lot for sharing this. It will be of great help in the future. Some of the work that I do includes calculators for financial models. I tend to use tables, not because I need to filter or sort, but mostly because formulas will make much more sense. Very often I need to refer to cells in the previous rows of the table. Next time, I will use the tricks that you both shared. ;-)