Moving months to quarter

I have a table with monthly data. Every month a new month is added and the oldest is dropped. It covers 24 months. I now need a view of this data on a quarterly base, showing only full quarters per year (in a seperate table). Because of the moving nature of the monthly data i have no idea how to do this and would appreciate your help. The headers for the columns are formatted as dates: 2012/sep.

Above a small sample from the monthly view. So for 1st row the quarters would be: 2012 Q1 to Q3. for the second row only 2012 Q1 and Q2. 2011/dec is only a partial so not visible in quarter view, same for 2012/oct and missing data data in row 2.

And this is what the (as a result also moving) quarterlies would look like. At most 8 full Q's are possible. 3 months to a quarter.

-
please add some screenshots of what I have and what I want, this is the easiest way to get your point ;) – Jook Nov 1 '12 at 13:38

I am still not sure about how it should look like when it is ready, but this formula might help you to get there:

``````="Q"&ROUNDUP(MONTH(P3)/3,0)
``````

That way you can determine, which quarter a date is in.

Because it is based on the date, it is dynamic, and as the dates move, the quartes move accordingly. So you should have it easy.

Edit

Ah ok, getting a picture of it. You will have to use the `ROUNDUP` part in a `SUBTOTAL` with an `OFFSET`, that way you could slide the subtotal through your data.

You could also use `SUMIF`, when you provide a row with the current quarter for each date.

Ans this http://stackoverflow.com/questions/13175801/find-and-sort-same-months-name-as-single-in-microsoft-excel-2007/13176826#13176826 might inspire your solutions, because it is a quite equal situation.

-
I (think I) understand the offset in the subtotal. but i cant figure out how to get the first reference in the subtotal. =SUBTOTAL(9,V5,OFFSET(V5,0,1),OFFSET(V5,0,2)). How do i find V5 using the roundup part. – Detnuomnu Nov 1 '12 at 19:22