- 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.