I have a series of monthly dates formatted as 01/01/2006, 02/01/2006, etc. to
most recent data point in column A. In column B I have data. In column C I
would like to calculate a rolling YTD number that is the sum of the YTD data
from this year (based on the latest data point/date that is populated in
column B) and divide this by the sum of the previous year's similar YTD sum.
(i.e. if the latest data point is for 03/01/2008, the calculation would be
(03/01/08 data +02/01/08 data + 01/01/08 data)/ (03/01/07 data +02/01/07 data
+01/01/07 data). I have been working with OFFSET and COUNTA, but I am
definitely not there. Any help would be greatly appreciated.

Not sure what the 2006 data has to do with this. but...

Name the cell in column B adjacent to 1/1/06 as BOY_2006, and adjacent to
1/1/07 as BOY_2007. Ditto for 1/1/08. (BOY means Beginning Of Year)

Now, your YTD sum for any month in 2008 will be

=sum(offset(boy_2008,0,0,month(Ax),1))
where x in the Ax is the row in which the current month's date appears in
column A.

if you want to divide by the earlier year's YTD, then the formula above
divided by

sum(offset(boy_2007,0,0,month(Ax),1))

"Marena" wrote:

I have a series of monthly dates formatted as 01/01/2006, 02/01/2006, etc. to
most recent data point in column A. In column B I have data. In column C I
would like to calculate a rolling YTD number that is the sum of the YTD data
from this year (based on the latest data point/date that is populated in
column B) and divide this by the sum of the previous year's similar YTD sum.
(i.e. if the latest data point is for 03/01/2008, the calculation would be
(03/01/08 data +02/01/08 data + 01/01/08 data)/ (03/01/07 data +02/01/07 data
+01/01/07 data). I have been working with OFFSET and COUNTA, but I am
definitely not there. Any help would be greatly appreciated.

The 2006 data was intended as an example and also to bring home the point
that I want the ytd to be calculated properly regardless of what year it is
currently. So, in 2009 I won't need to change the formula again. I am
working currently with two offset formulas to determine proper data points
and then summing them, but I am not all the way there yet. Thank you for
your help though.

"Duke Carey" wrote:

Not sure what the 2006 data has to do with this. but...

Name the cell in column B adjacent to 1/1/06 as BOY_2006, and adjacent to
1/1/07 as BOY_2007. Ditto for 1/1/08. (BOY means Beginning Of Year)

Now, your YTD sum for any month in 2008 will be

=sum(offset(boy_2008,0,0,month(Ax),1))
where x in the Ax is the row in which the current month's date appears in
column A.

if you want to divide by the earlier year's YTD, then the formula above
divided by

sum(offset(boy_2007,0,0,month(Ax),1))

"Marena" wrote:

I have a series of monthly dates formatted as 01/01/2006, 02/01/2006, etc. to
most recent data point in column A. In column B I have data. In column C I
would like to calculate a rolling YTD number that is the sum of the YTD data
from this year (based on the latest data point/date that is populated in
column B) and divide this by the sum of the previous year's similar YTD sum.
(i.e. if the latest data point is for 03/01/2008, the calculation would be
(03/01/08 data +02/01/08 data + 01/01/08 data)/ (03/01/07 data +02/01/07 data
+01/01/07 data). I have been working with OFFSET and COUNTA, but I am
definitely not there. Any help would be greatly appreciated.