How do I convert monthly data to quarterly data?

[Guest]

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

 

[PokerZan]

I'm 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:

1. 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 knowlegdge there is no formula for dertimining 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.

Pzan.

 

[Jay]

I would like to convert high frequency (such as monthly) time series

data to lower frequency (such as quarterly) data.

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.

------------- cut here ---------------
,,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)"