How to Group Irregular Date Data into Monthly Averages in WPS Office Spreadsheet
Question details
The user needs to turn a list of readings with irregular dates and missing dates into a monthly average summary.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A date-based dataset has inconsistent sampling intervals and missing days or weeks, and the user wants one average value per calendar month.
- Observed behavior
- Raw data cannot be averaged by fixed row ranges because dates do not fall in regular intervals or complete months.
Open the workbook in WPS Office Spreadsheet and confirm the date column contains real date values instead of text. Select the date column and check Format Cells > Number > Date; convert text dates before grouping so month grouping works correctly.
Group Dates with a PivotTable and Show Monthly Averages
Recommended for an interactive monthly summary that does not alter the source data and updates when the data changes.
A PivotTable treats the date column as a field and can group it by month, even when source dates contain gaps or irregular intervals.
Click any cell in the date and reading range, go to the Insert tab, and click PivotTable. Confirm the table or range and choose where to place the PivotTable, then click OK.
In the PivotTable Fields pane, drag the Date field to the Rows area and the Reading field to the Values area. Click the reading field, select Value Field Settings, and choose Average.
Right-click any date in the PivotTable row labels, choose Group, select Months, and also select Years if the data spans multiple years, then click OK.
Select the average values, right-click, choose Format Cells > Number, and set the desired decimal places and number format.
Calculate Monthly Averages with a Helper Column and AVERAGEIF
Use this if you need a fixed summary table or want a formula-based approach that also works in older Excel versions and WPS Office Spreadsheet.
Group Irregular Dates into Monthly Averages in WPS Office Spreadsheet
Use WPS Office Spreadsheet to group irregular date readings by month with a PivotTable or AVERAGEIF helper column. WPS Office opens and saves Excel .xlsx files, so you can summarize monthly averages without changing your existing workflow.
- 1. Download and install WPS Office: Download WPS Office Free from the official website, then install and open WPS Office Spreadsheet.
- 2. Open your Excel workbook: Click Menu > Open and select the Excel workbook that contains the irregular date and reading data.
- 3. Insert a PivotTable and group by month: Click inside the data, go to Insert > PivotTable, add dates to Rows and readings to Values as Average, then group the date field by Months.
- 4. Save the summary: Click Menu > Save As and choose .xlsx to keep the workbook fully compatible with Microsoft Excel.
Frequently Asked Questions
Can I group irregular dates by month without a PivotTable in WPS Office?
Yes. Add a helper column with =TEXT(A2,"mmm-yyyy") or =EOMONTH(A2,0), then use AVERAGEIF over that helper column to return a monthly average for each month label.
Why does GROUPBY return a #NAME? error in WPS Office?
GROUPBY may not be available in all WPS Office editions. If your version does not support this dynamic array function, use the PivotTable or AVERAGEIF helper column method instead.
How do I exclude blank or zero readings from the monthly average?
Use AVERAGEIFS with an additional criteria such as $B$2:$B$35,"<>0" to ignore zero values. For blanks, ensure the PivotTable source range does not include empty rows or filter them out before grouping.
Can I group the same irregular date data by quarter or year?
Yes. In the PivotTable Group dialog you can select Months, Quarters, or Years. With formulas, change the helper column to return quarter or year labels and use the same AVERAGEIF or AVERAGEIFS approach.
