Converting monthly data into quarterly

Dear Excel experts,

I need to convert several series of monthly data (in rows, one range from Oct 1994-June 2009, i.e., in my sheet columns HD:NZ) into Quarterly data.

The Quarterly result should simply be the last month of that Quarter, so for Jan-Mar it should return March for Q1, Apr-Jun should return June for Q2, etcetera. There is, however, one caveat: if a quarter's last month does not contain any data or a value of '0' it should return the data for either the first or second preceding month. In the above examples: for Q1 either February or January, for Q2 either May or April.

Any tips? (No pivot tables or VBA please).

Many thanks in advance for your kind support!

Best regards,

Paul

something like
=IF(A1="",ROUNDUP(MONTH(LOOKUP(9.99999999E+307,$A$1:A1))/3,0),ROUNDUP(MONTH(A1)/3,0))
or
=CHOOSE(IF(A1="",ROUNDUP(MONTH(LOOKUP(9.99999999E+307,$A$1:A1))/3,0),ROUNDUP(MONTH(A1)/3,0)),"march","june","sept","dec")
if that doesn't do it post an example with what you want

i have no idea what you want! show a manually generated example

its doable but its a pig to drag the formula across so i did the first one dragged it across and deleted the cells i didn't need, then dragged what was left down
probably could make a draggable index with indirect but someone else may give that a go

Using your sample file (post #5)

Please Login or Register  to view this content.

above utilises IFERROR which is new to XL2007 - for prior versions:

Please Login or Register  to view this content.

edit: modify delimiters as required per locale config.

that gives me a headache lol

Originally Posted by paulgerrits

I have tried the suggestion of DonkeyOte and it works great though there seems to be one glitch for the returns given in Column S which gives data from a column too early in the sequence.

Something odd with your file there Paul - if you conduct a Full Recalc (CTRL + ALT + F9) you will find the cells will recalculate generating correct results.

Originally Posted by paulgerrits

how do I re-write the formula if the data would be in columns intead of rows?

If you can assume you always have a full quarter listed (ie 3 months each quarter - irrespective of whether or not values are 0) then using your latest sample:

Please Login or Register  to view this content.

Again, as before the above utilises IFERROR which is not backwards compatible with versions pre XL2007 - revert to:

Please Login or Register  to view this content.

if you need a backwards compatible version

of course given this layout you could actually just use an embedded IF if you prefer:

Please Login or Register  to view this content.