Difference between last column in a table and another column

[Spiru]

Hi guys,

Could you please help with any ideas with the below problem that I have?

I would like to calculate in the column where it says "Variance" the difference between last column where it finds values and the column before it (April -March). I have added in cell G1 a dropdown list (bold) with the months and I was thinking to use this cell in an If function..but I don't know how

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Variance [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]32[/TD]
[TD]54[/TD]
[TD]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]43[/TD]
[TD]76[/TD]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank a lot in advance

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	anuary	February	March	April	May	June				Variance
2	10	32	54	76						22
3	13	43	76	44						-32
Sheet18

Cell Formulas
Range	Formula
J2	=INDEX($A2:$F2,MATCH(9.99999999999999E+26,$A2:$F2,1))-INDEX($A2:$F2,MATCH(9.99999999999999E+26,$A2:$F2,1)-1)

If you want to choose which month then change the match lookup term to the cell reference, the match range to the months row section, and the type to 0.

 

[Spiru]

Thanks a lot it works! But I couldn't manage to change the formula in order to work with the month.

 

[Spiru]

the formula seems to work in some cells in my excel file but for my table it doesn't. In the table I have formulas witch populate the cells with numbers. I have tried to change the format in my columns to numbers, general, accounting or even text but the formula still doesn't work. Any idea why? Thanks a lot for your help

 

[Spiru]

it looks like the formula doesn't work because of the formulas that I have in column E (May)...although vlookup brings 0. Maybe using the month from the dropdown list like I said in my first post would make it work?

 

[sheetspread]

This one goes by the month in I1:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	January	February	March	April	May	June			April	Variance
2	10	32	54	76						22
3	13	43	76	44						-32
Sheet18

Cell Formulas
Range	Formula
J2	=HLOOKUP($I$1,$A$1:$F$3,ROW(A2),0)-INDEX($A2:$F2,MATCH($I$1,$A$1:$F$1,0)-1)

 

[Spiru]

thank you! It works