|Tip of the Month - March 2003||| Tip of the Month Archive ||
Using Excel to Dynamically Aggregate Data Series at Monthly Frequency to Quarterly Averages and Merge with Data Series at Quarterly Frequency
- Create one Excel Worksheet with monthly dates in column A and monthly
data in columns B and beyond as required. Enter column labels in row
- Create a second Excel worksheet and enter the quarterly data series
with column labels in row 1.
- Copy the data labels from the monthly worksheet into row 1 of the
- Enter the formula “=Average(Offset(Monthly!B$1,
(row()-1)*3-2,0,3,1))" in row 2 under the label of the first monthly
frequency data series in the quarterly worksheet. Note that Monthly
must be the name that appears on the tab of the worksheet in which the monthly
frequency data are stored.
- Drag the above formula to all the columns and rows for which quarterly averages of monthly data are available.
With this setup, any revisions to the monthly data series will automatically update the quarterly average data in the quarterly worksheet. When additional observations on the monthly data become available, the sample of quarterly averages can be extended by dragging the formula in these data columns on the quarterly worksheet down over additional rows.