Excel: Calculate the Average of Every 24 Rows
Question details
The user needs to automate averages for consecutive, non-overlapping groups of 24 values in a large spreadsheet.
- Product
- Microsoft Excel and WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The dataset contains values arranged in consecutive rows or a named range, and the user wants one average for each group of 24 values without selecting every group manually.
- Observed behavior
- A manual range such as AVERAGE(C2:C25) calculates only one group or creates a rolling average when copied, rather than returning separate averages for each 24-row block.
Confirm whether the values are arranged horizontally or vertically, and identify the exact source range or named range. Decide whether the final partial group should also be averaged when fewer than 24 values remain.
Use BYROW and WRAPROWS for consecutive groups in Microsoft 365
Use this approach when the values are in a horizontal range or named range and your spreadsheet supports dynamic-array functions.
This formula creates rows of 24 values and returns one average for each row. It calculates non-overlapping groups, not a rolling average.
Replace data with the actual range, such as C2:C121, or use an existing named range. The final group can contain fewer than 24 values if the range does not divide evenly.
Open the worksheet in Excel or WPS Spreadsheet and click the blank cell where the first grouped average should appear.
Type =BYROW(WRAPROWS(data,24),LAMBDA(x,AVERAGE(x))) and replace data with the source range or named range.
Press Enter. The spreadsheet should spill one average per group into the cells below the formula.
Calculate averages for a vertical range with LET, SEQUENCE, and MAP
Use this formula when the source data is vertical and you need one result for every consecutive block of up to 24 rows.
Calculate grouped 24-row averages in WPS Spreadsheet
WPS Spreadsheet lets you enter Excel-compatible grouped-average formulas, fill or spill the results, and save the completed workbook in XLSX format for easy sharing and continued editing.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the 24-row dataset.
- 2. Choose the output location: Click an empty cell beside or below the source data where the grouped averages should begin.
- 3. Enter the formula: Type =BYROW(WRAPROWS(data,24),LAMBDA(x,AVERAGE(x))) using your actual range in place of data, then press Enter.
- 4. Check and save the results: Review the spilled averages for each group and select File > Save As to save the workbook as an XLSX file.
Frequently Asked Questions
How do I average every 24 rows in Excel without creating a rolling average?
Use =BYROW(WRAPROWS(data,24),LAMBDA(x,AVERAGE(x))) in Microsoft 365, replacing data with the source range. This returns one result for each non-overlapping group of 24 values.
Can I calculate grouped averages from a vertical range?
Yes. Use the LET, SEQUENCE, UNIQUE, INDEX, and MAP formula provided for vertical data. Enter it in an empty output cell and replace data with the vertical source range.
What is the difference between a grouped average and a rolling average?
A grouped average returns one result for rows 1-24, another for rows 25-48, and so on. A rolling formula such as =AVERAGE(C2:C25), copied down, recalculates for each row using the current row and previous 23 rows.
What happens if the dataset has fewer than 24 values in the last group?
The dynamic-array formulas can average the remaining values in the final partial group. If you want to exclude incomplete groups, filter or limit the source range to a multiple of 24 before calculating.
