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!

 

[Sufiyan97]

Hi

Not getting the query, can you please manually type what error you are currently getting and what is your expected result.

 

[RockandGrohl]

Hi

Not getting the query, can you please manually type what error you are currently getting and what is your expected result.

Hi, I need a formula that considers cell position rather than cell reference.

So if my formula is in cell T6, "=S6-R6", it would be fine up until I insert a column in Column S, which then means my formula is in cell U6, but it's still referencing =S6-R6 - meaning there's a gap.


If my formula instead said "Value to left of current cell subtract value two cells left of current cell" this would work, because no matter how many columns I insert, it doesn't change.

Does that make sense or are you even more confused lol.

 

[Sufiyan97]

Try

Book3
	P	Q	R	S	T	U
1
2
3
4
5
6
7			1	1	0
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
T7	T7	=INDEX($R7:S7,,COUNT($R7:S7))-INDEX($R7:S7,,COUNT($R7:S7)-1)

 

[RockandGrohl]

Try

Book3
	P	Q	R	S	T	U
1
2
3
4
5
6
7			1	1	0
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
T7	T7	=INDEX($R7:S7,,COUNT($R7:S7))-INDEX($R7:S7,,COUNT($R7:S7)-1)

Hi, unfortunately not because as soon as you insert a new column in between, the formula is still referencing the old cells.

Here, -4171 is the variance cell. 34,993 going to 30,822 is a variance of -4171

When April's data is available, I insert a new column, and this means I have to move the formula references over.

If instead the formula only considered the two cells immediately left, then it wouldn't matter if I added or removed columns, it would always give me the variance for the last 2 dates.

 

[RockandGrohl]

Found it.

This is what I want.

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)-OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-2)

Thanks for trying anyway mate. If anyone knows a way to condense it so it's simpler for someone else to read and understand, I'm all ears.

 

[Fluff]

How about

=INDEX(6:6,,COLUMN()-1)-INDEX(6:6,,COLUMN()-2)

 

[RoryA]

Found it.

This is what I want.

Excel Formula:
=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)-OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-2)

Simpler to use:

=INDIRECT("RC[-1]",0)-INDIRECT("RC[-2]",0)

 

[Alex Blakenburg]

Some more options:

Book1
	R	S	T	U
1	Amt1	Amt2	2 minus 1
2	1000	100	-900	Current formula
3	1000	100	-900	Offset
4	1000	100	-900	Index
Sheet1

Cell Formulas
Range	Formula
T2	T2	=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)-OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-2)
T3	T3	=OFFSET(T3,0,-1)-OFFSET(T3,0,-2)
T4	T4	=INDEX($A4:$T4,0,COLUMN()-1)-INDEX($A4:$T4,0,COLUMN()-2)

 

[RockandGrohl]

How about

=INDEX(6:6,,COLUMN()-1)-INDEX(6:6,,COLUMN()-2)

Yeah that's nice and clean, thank you!