Home > Tips and Tutorials > March 2003

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

1. 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 1.
   
   
   
   
2. Create a second Excel worksheet and enter the quarterly data series with column labels in row 1.
   
   
3. Copy the data labels from the monthly worksheet into row 1 of the quarterly worksheet.
   
   
   
   
4. 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.
   
   
5. 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.