Create Weekly Station Averages with an Excel PivotTable
Question details
Calculate average weather measurements by station and week from multi-year data.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A user wants to summarize weather-station measurements, such as temperature and precipitation, by station and week.
- Observed behavior
- Each station and week should show the average of each selected measurement rather than its sum.
Check that your data has column headers, station identifiers, dates or week numbers, and numeric measurement values. Decide whether matching week numbers from different years should be combined or reported separately.
Summarize station measurements with an Excel PivotTable
Use a PivotTable to group records by station and week and calculate averages for multiple measurements.
A week number alone combines the same numbered week across all years. Add a year field if you want each year's weeks reported separately.
If your data has dates but no week column, add a Week header and enter =WEEKNUM(B2,2) in the first data row, replacing B2 with the date cell. Fill the formula down the column.
Select a cell in the source data, then choose Insert > PivotTable. Confirm the data range and choose where to place the PivotTable.
In the PivotTable Fields pane, drag Station and Week to Rows. If you need separate results by year, add a Year column to the source data, refresh the PivotTable, and drag Year to Rows as well.
Drag each measurement field, such as Temperature or Precipitation, to the Values area.
For each field in Values, open Value Field Settings, select Average under Summarize Values By, and click OK.
Calculate station and week averages with AVERAGEIFS
Use formulas when you want averages in a custom summary table instead of a PivotTable.
Create Weekly Station Averages in WPS Spreadsheets
Use a PivotTable in WPS Spreadsheets to group weather data by station and week and summarize measurement fields as averages.
- 1. Open the data: Open your Excel workbook in WPS Spreadsheets and check that the source table includes Station, Week, and numeric measurement columns.
- 2. Insert a PivotTable: Select a cell in the data and choose Insert > PivotTable. Confirm the source range and destination.
- 3. Arrange the fields: Drag Station and Week to Rows, and drag each measurement field to Values. Add a Year field to Rows if years must remain separate.
- 4. Set the calculation: Open the settings for each field in Values and select Average as the summary calculation.

Frequently Asked Questions
How do I change a PivotTable from sum to average?
In the PivotTable Fields pane, open Value Field Settings for the measurement in Values, select Average, and click OK. Repeat for each measurement.
Will week 1 from different years be combined?
Yes, if you group only by station and week number. Add a Year field to the source data and the PivotTable Rows area to keep years separate.
How can I create a week number from a date?
Add a Week column to the source data and enter =WEEKNUM(B2,2), replacing B2 with the date cell. Fill the formula down, then refresh the PivotTable. Use a different week convention if your reporting requires it.
Can I calculate averages without a PivotTable?
Yes. Use AVERAGEIFS with the measurement column as the average range and the station and week columns as criteria ranges. Add a year criterion if needed.




