Google Sheets Average of Each Row

1
votes

This formula gives a single average result from the whole range, also is not dynamic.

=AVERAGE(FILTER($B:$E,ARRAYFORMULA(ROW($B:$E)>COUNT($B:$E))))

What I need from the formula is to:

  • Calculate 1 average result per row, from the second row downwards.
  • Count only the first 4 cells with values in the row, ignoring blank cells.

Example

Demo sheet

3
google-sheetsgoogle-sheets-formulaaveragearray-formulas
Please remember in the future, sharing a sample sheet, in addition to an image, saves people having to re-enter your data, to test out their solution. Thanks. - kirkg13
@kirkg13: can you show us the expected outcome? Just to make sure we understood correctly... - JPV

3 Answers

1
votes

use in B4:

=query(transpose(query(transpose(C4:H), 
 "select "&textjoin(",", 1, ArrayFormula(if(len(A4:A),
 "avg(Col"&ROW(A4:A)-ROW(A4)+1&")", )))&"")), 
 "select Col2")
2
votes

Simpler option, must be copied down:

=AVERAGE(ARRAY_CONSTRAIN((FILTER(C2:2,ISNUMBER(C2:2))),1,4))

enter image description here

1
votes

Does this work for you? Try this formula in B4, and drag down.

=TRANSPOSE(QUERY(
             QUERY({TRANSPOSE(C4:H4)},"select Col1 where Col1 >0 limit 4",0),
             "select sum(Col1)/count(Col1) where Col1>0 label sum(Col1)/count(Col1) ''",0))

Let us know of any issues. I'll add some explanation if this seems to work.

enter image description here