How to Average Every Four Cells in Excel Without Skipping Rows
Question details
The user needs to calculate the average of consecutive blocks of four cells and use autofill without breaking the sequence.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Applying an average formula to grouped data ranges (every 4 rows) continuously down a new column using the autofill handle.
- Observed behavior
- Standard autofill shifts cell references down by exactly one row (e.g., Z3:Z6 instead of Z6:Z9), resulting in overlapping averages instead of discrete four-cell blocks.
Ensure your data is organized in a continuous column without blank rows between the values you intend to average, as blank cells can distort the calculation.
Use the INDEX and ROWS Function Combo
This dynamic formula accurately calculates the exact start and end row positions of each 4-cell block, ensuring the reference jumps by four rows when dragged down.
The standard autofill behavior shifts references row-by-row. By combining INDEX and ROWS, you can create a mathematical sequence that forces the range to jump four cells at a time.
Click on the cell where you want the first average result to appear (for example, cell AA2).
Input the formula: =AVERAGE(INDEX($Z:$Z,(ROWS($AA$2:AA2)-1)*4+2):INDEX($Z:$Z,ROWS($AA$2:AA2)*4+1)). Note: Change $Z:$Z to match your actual data column, and $AA$2:AA2 to match your starting destination cell.
Press Enter to calculate the first block. Then, click and hold the small square at the bottom right corner of the cell, and drag it downward to calculate averages for subsequent four-cell blocks.
Use the OFFSET Function
The OFFSET function is another dynamic way to shift the averaging range by a specific number of rows relative to your starting position.
Use WPS Spreadsheet to Handle Dynamic Data Ranges
WPS Spreadsheet fully supports advanced dynamic array formulas like INDEX, OFFSET, and AVERAGE, enabling you to calculate complex range groups effortlessly without syntax errors.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing the data column.
- 2. Select target cell: Click on the cell where you want the averaged result to be shown.
- 3. Apply dynamic formula: Paste the INDEX or OFFSET formula into the formula bar and adjust the column letters if necessary.
- 4. Drag to fill: Drag the fill handle downwards; WPS Spreadsheet will correctly compute the consecutive 4-cell averages.
Frequently Asked Questions
Why does normal autofill fail when averaging grouped rows?
By default, dragging a formula down one row shifts the cell references exactly one row down (e.g., Z2:Z5 becomes Z3:Z6). It does not automatically recognize that you want to skip entirely to the next distinct block of four rows (Z6:Z9).
Can I change the formula to average every 5 or 10 cells instead?
Yes. You simply need to change the multiplier in the formula. For example, replace the '*4' with '*10' in the INDEX or OFFSET formula to average every 10 cells sequentially.
Does this formula work if my data starts in row 1?
If your data starts in row 1 instead of row 2, you must adjust the constant added at the end of the INDEX calculations. For instance, you would adjust the formula to calculate +1 for the start point and +0 for the next step, aligning with row 1 boundaries.
