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

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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

anthonyfca

New Member
Joined
Jan 12, 2012
Messages
8
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

New Member
Joined
Jan 12, 2012
Messages
8
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.
 

Forum statistics

Threads
1,148,193
Messages
5,745,272
Members
423,941
Latest member
CluelessAboutExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top