Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

enter image description here

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.

enter image description here

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.

share|improve this question
1  
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

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.