anthonyfca
New Member
- Joined
- Jan 12, 2012
- Messages
- 8
My formula cumulates a running total without using an A1 reference for the row above, so that when that row above is moved or deleted or sorted, the formula still refers to the cell just one above. All the functions that involve A1 references (or RC type) fail because when moved, the reference changes with it - or is deleted and we get #REF.
Column A is a list of values, Column B is the running total (think bank balance).
So in B2 I have
=A2+OFFSET(B2,-1,0)
My aim is to avoid any references that are not on the same row - Row 2 in the above example.
I have not looked at INDIRECT because it too is volatile.
INDEX, MATCH, COUNTIF(S) all seem to require the cell above's reference is used somewhere in their use.
I see several questions have been asked around OFFSET but none seem to deal with this particular detail.
Regards
Anthony
Column A is a list of values, Column B is the running total (think bank balance).
So in B2 I have
=A2+OFFSET(B2,-1,0)
My aim is to avoid any references that are not on the same row - Row 2 in the above example.
I have not looked at INDIRECT because it too is volatile.
INDEX, MATCH, COUNTIF(S) all seem to require the cell above's reference is used somewhere in their use.
I see several questions have been asked around OFFSET but none seem to deal with this particular detail.
Regards
Anthony