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.
Hi, PeterBartholomew1 and @Wyn Hopkins!
Could you please explain the steps in more detail?
When I try to set the name prior as referring to =Sheet1!R[-1], I get an error message saying that the formula is not valid.
I tried =Sheet1!R[-1]C but I got the same error.
If I set up the name by selecting A2 and referring to A1 (no dollar sign), the formula is accepted. But I cannot get it to work with the table field names as prior [balance]. It will only work if I use the name alone (no table field associated) to refer to the previous row on the same column.
Any clarification/help would be very much appreciated.
Thanks! :)
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!
need help.
we have inventory movement table for various stock items with stock In and Out information.
I am try to create a pivot report, where we can new fields to table such as opening balance, running balance and closing balance for any given point in a time.
can someone help, how to write a formula for opening balance, running balance and closing balance.
see attached spreadsheet for example
Unless Wyn Hopkins wishes your question to be dealt with here, I suggest you open a new thread.
I would also observe that since there are some of the most competent Excel practitioners worldwide responding to this thread it would be a mistake to narrow your potential solutions to a specific approach.
R1C1 notation is more suitable and more natural for named relative reference. Not necessary to think about implicit intersection. However, that works fine in A1 notation as well.
In A1 notation exactly the same will be if, for example, you stay on cell A2 and add named reference RowAbove as
=Sheet1!A1
with relative references. You may stay now on any other cell, let say K5 and check in name manager what is the formula for RowAbove - now it will show Sheet1!K4.
Same for any other relative references. It's important to take into account where do you stay adding such named references.
With the R[-1] method I may be wrong but I think you have to go to Options > Formulas and set it to R1C1 method
