1

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.

0

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

| improve this answer | |
  • This seems to work, but I oversimplified a bit in my question. I actually need to start at row 2 because I have a header row. I tried to modify this to be 2, 5, 8, etc. but I wasn't successful. – Jake Munson Aug 5 at 2:15
  • it doesn't appear to work. You do have an error in your parentheses, but I fixed that and the averages still don't match the real numbers. – Jake Munson Aug 5 at 2:34
  • @Jake Is it okay now? – Severus Snape Aug 5 at 4:14
  • 1
    Yes, thank you very much! I had still over-simplified my example too much in my question so I had to modify your answer to fit my needs, but after that it worked! Great formula! For reference, this is the one that worked for me: =AVERAGE(INDIRECT(CONCAT("A",(3*(ROW()-1)-1))):INDIRECT(CONCAT("A",(3*(ROW()-1)+1)))) – Jake Munson Aug 5 at 22:48
  • I am glad it helped you. 🙂 – Severus Snape Aug 6 at 2:22
0

See Test sheet

Formula in cell D2 returns average value for each 3 cells in column C (however in cell D1 you can specify number or if you need header remove it and change reference in formula to value)

=ARRAYFORMULA(
VLOOKUP(
 SEQUENCE(ROUNDUP(COUNT(C2:C)/D1),1,1,1),
 {ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN("",REPT(SEQUENCE(ROUNDUP(COUNT(C2:C)/D1),1,1,1)&",",D1)),","))),
 ARRAYFORMULA(SUMIF(
  ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN("",REPT(SEQUENCE(ROUNDUP(COUNT(C2:C)/D1),1,1,1)&",",D1)),","))),
  ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN("",REPT(SEQUENCE(ROUNDUP(COUNT(C2:C)/D1),1,1,1)&",",D1)),","))),
C2:C)/D1)},2,0))

Idea is to generate labels for data based on required number, e.g.

    A    Label
1   32   1 
2   25   1
3   10   1
4   14   2
5   87   2
6   32   2
7   5    3
8   97   3
9   73   3

Following formula generates those labels

ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN("",REPT(SEQUENCE(ROUNDUP(COUNT(C2:C)/D1),1,1,1)&",",D1)),",")))

Using SUMIF (unfortunately AVERAGEIF doesn't work with arrays) we get sum of 3 cells based on previos labels, then we divide it by value in cell D1 (in our case it's 3) and get average.

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

Since we don't need repeated 3 times average values, we use VLOOKUP to get average values for all labels.

| improve this answer | |
0

Another, just for fun:

=ArrayFormula(ROUND(MMULT(VLOOKUP(SEQUENCE(ROUNDUP(COUNTA(A:A)/3),3,2,1),{ROW(A:A),A:A},2,FALSE),SEQUENCE(ROUNDUP(COUNTA(A:A)/3),1,1,0))/3))

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.