Google Sheets – How to Calculate Average of Every 3 Rows

google sheets

Say I have a sheet like this:

    A    B
1   32   22 
2   25   44
3   10   58
4   14
5   87
6   32
7   5
8   97
9   73

The sheet in reality has hundreds of rows. I need a formula that averages the first 3 cells of column A, and then if I copy this formula to the next row it would average A4:A6, and then the next row would average A7:A9, and so forth. My desired result is shown in column B. Is such a thing possible? I've tried all kinds of variations using INDIRECT, ADDRESS, etc. But nothing so far has worked.

Best Answer

The starting row numbers are assumed to be 1, 4, 7, and so on. In general it is 3n-2

The second row number is 3n-2+1, and the third is 3n-2+2

This is the formula:

=(indirect(concat("A",(3*(row())-2))) + indirect(concat("A",(3*row())-2+1)) + indirect(concat("A",(3*row())-2+2))) / 3

Here is a sample screenshot:

E1

If the starting row numbers are 2,5,8.. then the generalised form is 3n-1 for the first row, 3n for the second, 3n+1 for the third. But again inputs from row() would start at 2. So, we would need to deduct 1. Thus, (3*(row()-1)-1 is 3(n-1)-1 and so on.

This is the formula:

=(indirect(concat("A",(3*(row()-1)-1))) + indirect(concat("A",(3*(row()-1)))) + indirect(concat("A",(3*(row()-1)+1)))) / 3

Example 2:

E2

Can be done in this way as well:

=AVERAGE(INDIRECT(CONCAT("A",(3*(ROW()-1)-1))):INDIRECT(CONCAT("A",(3*(ROW()-1)+1))))

Final example:

E3

Related Topic