Function Problems

How to Calculate Excel Averages for Non-Overlapping Groups of Rows

Nimra Malik Sep 27, 2026 869 views

Question details

The user needs to calculate the average of non-overlapping, sequential groups of three rows in a column.

How to Calculate Excel Averages for Non-Overlapping Groups of Three Rows

Product: Excel
Device & OS: not provided
Scenario: Summarizing or analyzing data by taking the average of distinct sequential blocks (e.g., rows 1-3, rows 4-6, rows 7-9) using a single formula that can be dragged down a column.
Observed behavior: Instead of generating standard moving averages with overlapping ranges, the goal is to skip three rows at a time sequentially while copying the formula down.

Before you start

Ensure your data is organized in a single continuous column without interspersed blank rows, and take note of the exact starting cell of your data range and the cell where your first formula will be placed.

Solution 1Recommended

Use the AVERAGE and OFFSET Formula

The OFFSET function dynamically shifts the reference range down by a specified number of rows for each cell the formula is copied to.

This method uses the ROW function to calculate how many blocks to skip based on the current cell's position, ensuring it multiplies dynamically as you drag it down.

Select the target cell

Click on the cell where you want the first average to appear (for example, cell C1).

Enter the OFFSET formula

Type the formula: =AVERAGE(OFFSET($A$1,(ROW()-ROW($C$1))*3,0,3,1)). Replace $A$1 with the first cell of your data and $C$1 with the cell address where you are typing the formula.

Apply to remaining cells

Press Enter, then click and drag the fill handle (the small square at the bottom-right corner of the cell) down to calculate averages for the subsequent groups of three rows.

Adjusting Group Size: If you want to average groups of 5 rows instead of 3, simply change the '3' in the formula to '5' in both places it appears.

Solution 2

Use the AVERAGE and INDEX Formula

A non-volatile alternative using the INDEX function, which can be more efficient and faster for very large datasets.

Solution 3

Use SEQUENCE for Dynamic Arrays (Newer Excel Versions)

For modern versions of Excel or WPS Office, you can use dynamic arrays with the SEQUENCE function.

Advanced Data Analysis

Easily Calculate Grouped Averages in WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced mathematical, reference, and dynamic array functions like OFFSET, INDEX, and SEQUENCE. You can seamlessly calculate non-overlapping row averages just as you would in Microsoft Excel, with zero learning curve.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data column.
2. Select the destination cell: Click on the cell where you want the first non-overlapping average to be displayed.
3. Input the formula: Go to the formula bar and paste =AVERAGE(OFFSET($A$1,(ROW()-ROW($C$1))*3,0,3,1)), updating references to match your data.
4. Fill down the column: Press Enter, then drag the bottom-right fill handle down to apply the calculation to the rest of your row groups.

Fully compatible with Microsoft Excel formulas and .xlsx formatsSupports advanced functions (OFFSET, INDEX) and dynamic arraysFree, lightweight, and features a user-friendly interfaceBuilt-in function wizard to help troubleshoot formula errors

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the group size from 3 rows to 5 rows?

Yes. To change the group size, replace the number '3' in the OFFSET formula with '5'. The modified formula would be =AVERAGE(OFFSET($A$1,(ROW()-ROW($C$1))*5,0,5,1)).

Why does the OFFSET formula use the ROW() function?

The ROW() function returns the row number of a cell. By subtracting the starting cell's row number from the current cell's row number, the formula creates a sequential counter (0, 1, 2, etc.). Multiplying this by 3 ensures the formula jumps exactly 3 rows down the source column every time you drag it down one cell.

Why am I getting a #DIV/0! error when dragging the formula down?

This error occurs when the formula calculates an average for a group of rows that are completely empty or contain text instead of numbers. You can avoid this by wrapping your formula in IFERROR, like this: =IFERROR(AVERAGE(...), "").

Is there a way to average non-overlapping groups without formulas?

Yes. You can create a 'Helper Column' next to your data and manually enter or generate group identifiers (e.g., 1,1,1, 2,2,2, 3,3,3). Once grouped, you can insert a Pivot Table and set the helper column as Rows and the data column as Values, changing the summarization setting from Sum to Average.

Excel & Data Problems