How to Average Every Group of 59 Values in a WPS Excel Row
Question details
The user needs to calculate separate averages for every consecutive group of 59 values in an Excel row, continuing until the data ends, and to consider a pivot table for hourly data.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a long row of values and wants group averages without manually selecting each 59-cell block.
- Observed behavior
- The task requires a repeatable formula or pivot table approach because manually averaging each group is inefficient.
Confirm whether the source values are in one row or one column and that the cells are formatted consistently as numbers. Identify the first data cell and the group size so the formula references can be adjusted to the actual layout.
Use a Fillable AVERAGE-OFFSET Formula for Fixed 59-Value Groups
Best when every group is exactly 59 consecutive cells in a row and you want one formula that can be dragged right across the worksheet.
The OFFSET version starts at the first data cell, moves 59 columns for each formula cell filled to the right, and returns the average for that 59-cell block. If your first value is not in A1, change $A$1 to the actual first data cell and leave the width and height arguments as 1 and 59.
Open your workbook in WPS Office and click the worksheet that contains the row of values.
Click the blank cell below the first data cell, such as A2 if the data begins in A1.
Type =AVERAGE(OFFSET($A$1,0,(COLUMN(A1)-1)*59,1,59)) and press Enter. If the first value is not in A1, replace $A$1 with that cell.
Select the formula cell, grab the fill handle in the lower-right corner, and drag right until averages appear for all 59-value groups.
Use a PivotTable to Average Values by Hour
Best when the values represent hourly data or timestamps, because a PivotTable automatically groups and averages without fixed 59-cell blocks.
Calculate Group and Hourly Averages in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same AVERAGE, OFFSET, INDEX, and PivotTable tools you need to average every group of 59 values. Enter a formula in one cell, fill it across the row, and use a PivotTable when your data is timestamped.
- 1. Install WPS Office: Download and install WPS Office, then open your Excel workbook in WPS Spreadsheets.
- 2. Apply the fillable formula: Click the cell where the first group average should appear and enter the AVERAGE/OFFSET or INDEX formula.
- 3. Fill across the row: Use the fill handle to drag the formula right until all 59-value groups are covered.
- 4. Use a PivotTable for hourly data: If the data includes timestamps, click Insert > PivotTable, set the value field to Average, and group rows by hour or day.
- 5. Save in your preferred format: Save the workbook as .xlsx, .xls, or another supported format as needed.
Frequently Asked Questions
How does the 59-value group formula know where each group starts and ends?
The formula starts at the first data cell, such as $A$1, and uses COLUMN(A1)-1 to move 59 columns each time the formula is filled to the right.
What should I change if my row data starts in B1 instead of A1?
Replace $A$1 with $B$1 in the formula. The first group will then average B1:BH1, the next group will average BI1:DO1, and so on.
When is a PivotTable better than the fixed 59-value group formula?
Use a PivotTable when the values are hourly or timestamped because it groups by actual hour or date, handles missing records, and calculates averages without fixed block sizing.
Can WPS Office open and save these Excel formulas and PivotTables?
Yes. WPS Spreadsheets supports Excel formulas, PivotTables, and .xlsx/.xls files, so you can open, edit, and save the workbook without losing the calculation setup.
