Sum cells to left of current cell

[RockandGrohl]

For some reason I can't articulate this into google.

I'm using

=SUM($A6:INDEX(6:6,COLUMN()-1))

to sum all cells to the left of the cell where the formula is written.

I need something with a similar function, where I have a column at the end of a set of data which is subtracting one value from another.

Trouble is, whenever I insert a new column the existing formula doesn't keep up and I have to change it, a small irritation.

=BC5-BB5

So is there a way to say "Take the value immediately left of the current cell and subtract for the value two cells left of the current cell"?

I'm sure there's a better way to word that, I think I need to use cell references?

The end goal of this is to be able to insert as many columns in as I want to add data to a table, with the "Variance" column at the end cleanly updating to reference the new cells without me sorting it out over so many pages. Thanks!

 

[Alex Blakenburg]

More importantly it avoids Offset which is a volatile function.

 

[Fluff]

Glad we could help & thanks for the feedback.