Intense Excel Question: Calculate a rolling YTD/YTD that is dynamic?

September 11, 2008 8:23 AM

Intense Excel Question: Calculate a rolling YTD/YTD that is dynamic?

I have a series of monthly dates formatted as 01/01/2006, 02/01/2006, etc. to the 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.

posted by Carialle to Computers & Internet (10 answers total)

It looks like you want

=SUMIF(A1:A1000,"<="&TODAY(),B1:B1000)
posted by jeather at 8:38 AM on September 11, 2008

Response by poster: Thank you for the suggestion, however because I am trying to get a rolling sum of the year-to-date data based on the most recent data point the range is not constant.
posted by Carialle at 8:51 AM on September 11, 2008

Jeather was close, then. Try in C2 the following

=SUMIF(A:A,"<="&A2,B:B) - SUMIF(A:A,"<="&DATE(YEAR(A2),1,1),B:B)
posted by FuManchu at 9:11 AM on September 11, 2008

Oops -- the second "<=" should in fact be "<"
posted by FuManchu at 9:12 AM on September 11, 2008

1. to get the latest Date. MAX the entire column.

2. then use EDATE(Latest Date, -12) for the same day a year ago.

3. then use ARRAY formula to sum any data between those two Dates.
={(dates less than Latest Date) * (dates greater than year earlier)*(dataset array)}

4. Do somethign similar for the year before that.

5. Divide
posted by mary8nne at 9:13 AM on September 11, 2008

Oh, and the previous YTD would then be:

=SUMIF(A:A,"<>
The full equation for (This Year TD)/(Prev Year TD) is then:

=(SUMIF(A:A,"<="&A2,B:B) - SUMIF(A:A,"<"&DATE(YEAR(A2),1,1),B:B))/(SUMIF(A:A,"<="&DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)),B:B) - SUMIF(A:A,"<"&DATE(YEAR(A2)-1,1,1),B:B))
posted by FuManchu at 9:16 AM on September 11, 2008

Ick, bad HTML-Excel equation

Previous Year TD sum:
=SUMIF(A:A,"<="&DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)),B:B) - SUMIF(A:A,"<"&DATE(YEAR(A2)-1,1,1),B:B)
posted by FuManchu at 9:20 AM on September 11, 2008

Response by poster: Thank you to everyone for their help, however the key to this was that I wanted it to be automated and dynamic so that when I updated the sheet with new data it would automatically capture it. In case someone ends up wanting to do this is the future this is the 'crazy' formula I ended up using. I had to convert my dates to text then using the left code pulled the month, then based on the month I offset by the appropriate amount. I am happy to help anyone who needs on this. Unlike my example above in this formula the dates are in B and data in C.

=(SUM(OFFSET(C$8,COUNT(ifnumber(C$8:C$1000),C$8:C$1000)-LEFT(TEXT(OFFSET(B$8,COUNTA(B$8:B$1000)-1,0), "mmyy"),2),0):OFFSET(C$8,COUNT(ifnumber(C$8:C$1000),C$8:C$1000)-1,0))/SUM(OFFSET(C$8,COUNT(ifnumber(C$8:C$1000),C$8:C$1000)-LEFT(TEXT(OFFSET(B$8,COUNTA(B$8:B$1000)-1,0), "mmyy"),2)-12,0):OFFSET(C$8,COUNT(ifnumber(C$8:C$1000),C$8:C$1000)-13,0))-1)*100
posted by Carialle at 10:32 AM on September 17, 2008

? I'm confused about your equation, Carialle. It should give the same result as the last row of my equation above. Not sure why you needed OFFSETs in the end.

Also, is "ifnumber" supposed to be "ISNUMBER"? What function does it serve in the COUNT function? It seems like it shouldn't be there, and may affect the result.
posted by FuManchu at 12:17 PM on September 17, 2008

Response by poster: IFNUMBER and ISNUMBER appear to work the same way. I tested it the formula comes out the same.
posted by Carialle at 12:07 PM on October 10, 2008