Trying to aggregate weekly data into monthly average data

  • Thread starter Thread starter Karen 1144
  • Start date Start date
K

Karen 1144

I have several weekly price series (data is released every Wednesday)
for a 10 year period. I want to aggregate the weekly data into monthly
averages. It seems like it should be fairly easy to do, but I can't
seem to figure out how to go about it.

Any help would be greatly appreciated!! Thanks in advance!
 
Assuming you have a column with some kind of week
indication and the prices in another column:

If your week indication is in Excel date format (say in
column A, starting row 3), use an extra column and insert
formula:

=MONTH(A3) & "-" & YEAR(A3)

in row 3, then copy down.

Next use subtotals, and select function Average for every
change in the new column.

If your week indication is not in Excel date format, it
will require a different trick. In that case I need to
know in what format it is so I can help.

Nikos Yannacopoulos (nyannaco at in dot gr)
 
Assuming you have ranges named Years, Months and Sales, you may use this
formula:

=SUMPRODUCT((Years=1996)*(Months=1)*Sales)/SUMPRODUCT((Years=1991)*(Months=1
))
 
The date was in an Excel format & the subtotal function you suggested
Nikos worked great!

The only other question I have is can I get the subtotals & only the
subtotals together (elsewhere in the current spreadsheet or in a new
one)?
 
If you do the Data|subtotals option, you can use those outlining symbols at the
left to hide the details and just keep the subtotals.

Then select the range
Edit|goto|special|visible cells only
Edit|copy

and paste where you want to.

===
Another alternative:
Create a pivottable (data|pivottable).
Then you can group those dates into month and year.

A little experimentation with pivottables will make this a really easy
solution--but you have to play with them to see how neat they are.
 
Back
Top