Tables are fantastic but cumulative totals are a pain

Celia_Alves 

I rarely, if ever, use a direct cell reference, so it makes little difference to me whether my workbook is set to the familiar A1 notation or the earlier R1C1 notation.  Why I chose to use it here is that it is less ambiguous.  If your calculation is set to R1C1,

=Sheet1!R[-1]

is a formula that references the entire row immediately above the active cell with the formula.  If you switch to A1 notation, the reference will need to be defined as

=Sheet1!9:9 if the active cell is in row 10

=Sheet1!10:10 if the active cell is in row 11

etc.

This definition (with an adjective used as a name) is made with an eye on generating readable formulas, e.g.

= prior [balance]

is the intersection of the row above the formula and the [balance] column (any two range references separated by a space will return the intersection).  Since the row prior intersects all of the columns of the table, one could also use

=(prior [date])

to give the date of the previous transactions.

If your workbook is sufficiently small that you can afford to use volatile functions, then you could, as an alternative, define 'prior' by the formula

=OFFSET(Table1[@],-1,0)

i.e. the table row above the current one.  Sample formulas might include the following

= IF( ISREF(prior Table1[#Headers]), "", [@Date] - (prior [Date]) )

for the interval between two transactions (could be of relevance if you are fortunate enough to accrue interest on current balances) or

= IF( ISREF(prior Table1[#Headers]), BFwd, [@Amount] + (prior [Balance]) )

for a running balance.  Here the first row will be characterised by the range 'prior' coinciding with the [#Headers] row so, instead of generating an error, the formula will reference the 'balance brought forward'.

Will your colleagues be able to read your formulas?  They will be able to deduce the intent but they would run a mile if you suggested they should take responsibility for the workbook!