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.

5 Answers 5

-1

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

0
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))
0

Contiguous Results

=TOCOL(BYROW(A2:A, LAMBDA(r, IFERROR(
   1/(1/NOT(MOD(ROW(r)-ROW(),3)))*
   1/(1/(LEN(r)/LEN(r)))*
   AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0))))),1)

Non-Contiguous Results

To return the results in line with the current row being calculated (every 3rd row) simply remove the TOCOL function:

=BYROW(A2:A, LAMBDA(r, IFERROR(
   1/(1/NOT(MOD(ROW(r)-ROW(),3)))*
   1/(1/(LEN(r)/LEN(r)))*
   AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0)))))

Explanation

Below I refer to every third row starting from the row containing the formula as a calcRow(s) ("calculation" row(s)).

  1. Including or omitting TOCOL is what determines if the results will be contiguous.
  2. BYROW passes each row, one by one, into a LAMBDA function that assigns the variable r to the current row.
  3. The LAMBDA's formula applies the AVERAGE function to the values of three cells:
    • Current row: r
    • One down:   OFFSET(r,1,0)
    • Two down:   OFFSET(r,2,0)
  4. The LAMBDA's formula is designed to trigger an error if r is not in a calcRow and/or if r is an empty cell. IFERROR returns nothing in those cases.
  5. The first test 1/(1/NOT(MOD(ROW(r)-ROW(),3))) returns 1 if r is in a calcRow or a #DIV/0! error if not. A few functions and Sheets behaviors are leveraged to trigger these errors:
    1. First, the MOD function is used to determine if the current row number ROW(r) (after first subtracting the row number containing the formula) is a multiple of 3, ergo a calcRow:
      • calcRow: MOD(ROW(r)-ROW(),3)=0
      • nonCalcRow: MOD(ROW(r)-ROW(),3)<>0
    2. The result from the last step is converted to TRUE for calcRow and FALSE for nonCalcRow by wrapping it in the NOT function:
      • calcRow: NOT(MOD(ROW(r)-ROW(),3))=TRUE
      • nonCalcRow: NOT(MOD(ROW(r)-ROW(),3))=FALSE
    3. The third and last step includes the calculation 1/(1/n) (divide one by the inverse of a number). This calculation always returns n where n<>0 or #DIV/0! where n=0. Since Sheets will coerce TRUE to 1 and FALSE to 0 in math calculations, the TRUE/FALSE values from the previous steps can be combined with the calculation. If it returns 1 that can be multiplied by the rest of the formula without impact:
      • calcRow: 1\(1\TRUE)=1
      • nonCalcRow: 1\(1\FALSE)=#DIV/0!
  6. The second test checks that r contains a value and triggers an error if the cell is empty thereby limiting the original infinite range A2:A to only those calc_rows that contain a value. This is done using the LEN function.
    • LEN(r) will return the total characters in the current row.
    • Again 1/(1/n) is used again to trigger an error if LEN(r)=0
    • If 1/(1/LEN(r)) returns a number, it must be handled in some way as the number itself is of no use to the formula. Since dividing a number by itself returns 1 this is a way to convert the number to something that can be multiplied by the rest of the formula without impact. Where n<>0:
      • n/n = 1/(1/n)/n = 1
  7. So the LAMBDA's formula can essentially be simplified to the following use cases where only the first will return a value, the rest will error out and nothing will be returned:
    1. if a calcRow and LEN(r)<>0
      IFERROR(1*1*AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0)))
    2. if a calcRow and LEN(r)=0 (empty)
      IFERROR(1*#DIV/0!*AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0)))
    3. if a nonCalcRow and LEN(r)<>0
      IFERROR(#DIV/0!*1*AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0)))
    4. if a nonCalcRow and LEN(r)=0
      IFERROR(#DIV/0!*#DIV/0!*AVERAGE(r,OFFSET(r,1,0),OFFSET(r,2,0))
1
  • Formula modified to allow zero values in the first row.
    – Blindspots
    Jul 5, 2023 at 1:07
-1

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.

-1

Had the same problem and decided on a solution without a formula and thought I'd share. It's not very elegant but here it is:

Take the average of 3 cells and extend the formula, then we can filter for every 3rd.

create a column of 1,2,3,4....n

A second column modulo(1,2,3,4....n, 3)

filter the second column for values = 1

Example:

ID value
A 1.01
B 2.02
C 3.03
D 1.11
E 2.22
F 3.33

Calculate the mean of ABC in the first column and then drag the formula...

Add two new columns n (=1,2,3...length of data) & modulo (= mod(n), 3), then filter modulo == 1

ID value Mean n modulo ( = mod(n,3)
A 1.01 =Mean(A,B,C) 1 1
B 2.02 =Mean(B,C,D) 2 2
C 3.03 =Mean(C,D,E) 3 0
D 1.11 =Mean(D,E,F) 4 1
E 2.22 =Mean(E,F,G) 5 2
F 3.33 =Mean(F,G,H) 6 0

which will leave you the mean of ABC, DEF

Obviously not as elegant as using a formula but does seem a quick fix.

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jun 25, 2023 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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