Pivot Table Issues

Create Weekly Station Averages with an Excel PivotTable

Camila MilosovichCamila Milosovich Nov 22, 2026 868 views

Question details

Calculate average weather measurements by station and week from multi-year data.

Create Weekly Station Averages with an Excel PivotTable
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the week field

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.

2
Insert a PivotTable

Select a cell in the source data, then choose Insert > PivotTable. Confirm the data range and choose where to place the PivotTable.

3
Arrange the row fields

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.

4
Add measurements

Drag each measurement field, such as Temperature or Precipitation, to the Values area.

5
Change sums to averages

For each field in Values, open Value Field Settings, select Average under Summarize Values By, and click OK.

Check your week convention: WEEKNUM with a second argument of 2 starts weeks on Monday. If your reporting uses ISO weeks or another convention, use a matching week field and corresponding year field instead.
Analyze data in WPS Spreadsheets

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. 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. 2. Insert a PivotTable: Select a cell in the data and choose Insert > PivotTable. Confirm the source range and destination.
  3. 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. 4. Set the calculation: Open the settings for each field in Values and select Average as the summary calculation.
Summarize multiple weather measurements by station and weekOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceAnalyze data with free spreadsheet tools
microsoft office alternative - wps office

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.