Need help to convert monthly data to Quarterly in Microsoft Excel

[ELETTRA|]

I would like to convert high frequency (such as monthly) time series data to lower frequency (such as quarterly) data.

[GaganjyotTechie]

I am assuming that you have a column of dates (daily) that you also want to be able summarize for quarterly purposes in a Pivot Table or some other format. Probably the best way I have been able to come up with a solution in my experience is this. Create a helper row that gives you the month with the following formula (this assumes that you have the daily date in one of the columns of existing data): =text(A2,"mmmm"). If A1 is the cell at the top of the column that has the date in it, this formula will return a month name (i.e. September). If you are going to want to look at quarterly data over a period of several years, then you will also need a year column. Again a helper row will allow you do this with the following formula: =text(A2,"yyyy") This will return the year from the date cell.

Now to my knowledge there is no formula for determining quarters, so I would sort the data by month, then assign the proper quarter # to the corresponding months. This will allow you to do a pivot table on the quarters and only see data for quarters. If this isn't what you are looking for let me know a few more specifics and I will see what I can do to help.

[Essien]

One way is illustrated by the example in the CSV file below. If that doesn't suit your needs, more details about your requirements might help. Check the below illustration and try it.

---Quarter---

• Jan,57,=INT((ROW()+1)/3)
• Feb,232,=INT((ROW()+1)/3)
• Mar,76,=INT((ROW()+1)/3)
• Apr,90,=INT((ROW()+1)/3)
• May,32,=INT((ROW()+1)/3)
• Jun,6,=INT((ROW()+1)/3)
• Jul,98,=INT((ROW()+1)/3)
• Aug,34,=INT((ROW()+1)/3)
• Sep,21,=INT((ROW()+1)/3)
• Oct,56,=INT((ROW()+1)/3)
• Nov,89,=INT((ROW()+1)/3)
• Dec,90,=INT((ROW()+1)/3)

---Quarter,Sum,---

• 1,"=SUMIF(C2:C13,A17,B2:B13)"
• 2,"=SUMIF(C2:C13,A18,B2:B13)"
• 3,"=SUMIF(C2:C13,A19,B2:B13)"
• 4,"=SUMIF(C2:C13,A20,B2:B13)"