Hi All I am trying to make a formula and struggling please help

[PAul Birch]

Hi All

I have an excel spreadsheet that list performance month on month over a ever growing period. I am trying to insert a cell that will show the difference between the last filled in cell and the cell that was 12 months before. The spreadsheet looks like this

Name Jan08 Feb08 mar08 ..... jan09 Feb09 Mar09 Year on year

Paul .... 5...... 6 .....7 .............6....... 6.................. ?


I cannot work out the formula i would need to fill in year on year

Any help would be greatly appreciated

Paul

 

[ExcelNewb09]

Your Date Row is a named range called "DATES"
'You're currently in Column Z
'The Dates row is on row 2
'You're currently on row 8


something like this should get your number from the period last year.

=sumif(dates,date(year(z2)-1,month(z2),Day(z2)),$A8:Y8)

...I think...

 

[PAul Birch]

Hi Thanks for that

Unfortunately it gives me #Name , Also i am unsure how this would find the last filled cell.

thanks for trying

 

[ExcelNewb09]

Book1
	C	D	E	F
4	Jan'08	0.401859
5	Feb'08	0.316086
6	Mar'08	0.488338
7	Apr'08	0.18122
8	May'08	0.970694
9	Jun'08	0.869843
10	Jul'08	0.936446
11	Aug'08	0.046158
12	Sep'08	0.527318
13	Oct'08	0.997865
14	Nov'08	0.247856
15	Dec'08	0.406031
16	Jan'09	0.40	SUMIF($C$4:$C$20,(DATE(YEAR(C16)-1,MONTH(C16)+1,0)),$D$4:D15)
17	Feb'09	0.32	SUMIF($C$4:$C$20,(DATE(YEAR(C17)-1,MONTH(C17)+1,0)),$D$4:D16)
18	Mar'09	0.49	0.49
19	Apr'09	0.18	0.18
20	May'09	0.97	0.97
Sheet1