Replace volatile OFFSET with something not volatile, but keep the -1 idea.

[anthonyfca]

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

 

[jasonb75]

On the assumption that row 1 contains text headers and will never be deleted, =SUM(A$1:A2)

Even if row 1 is deleted it should still work, but there could always be a way to break it that I haven't encountered.

 

[anthonyfca]

Hi Jason, thanks for your reply.
Pure Genius. So simple.
Seems too easy! But I haven't broken it yet and I agree it doesn't look like it will and it speeds up my spreadsheet very nicely.
Thanks.
Anthony

 

[anthonyfca]

Even if row 1 is deleted it should still work

OK I see that Excel no longer generates an error if we delete the first or last cell in range; that's nice to discover.