1

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

player0
  • 124,011
  • 12
  • 67
  • 124
Preack
  • 123
  • 7
  • 1
    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 Nov 20 '20 at 13:58
  • @kirkg13: can you show us the expected outcome? Just to make sure we understood correctly... – JPV Nov 22 '20 at 09:07

4 Answers4

2

Simpler option, must be copied down:

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

enter image description here

basic
  • 11,673
  • 2
  • 9
  • 26
1

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

kirkg13
  • 2,955
  • 1
  • 8
  • 12
1

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")
player0
  • 124,011
  • 12
  • 67
  • 124
  • Found a problem with your formula in some instances. If there's a value in column A but none in the same row from C onwards then the formula doesn't work. Added a demo sheet. Let me know if you think is better to open a new question to discuss further the frmula. – Preack Nov 23 '20 at 23:30
  • 1
    @Preack fixed. see your sheet – player0 Nov 23 '20 at 23:51
  • You are the blasting Boss. – Preack Nov 24 '20 at 18:16
0

new alternative:

=INDEX(IFERROR(1/(1/BYROW(OFFSET(C2,,,
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*ROW(C2:C)), 6), 
 LAMBDA(x, AVERAGE(x))))))

enter image description here

see more at: ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets

player0
  • 124,011
  • 12
  • 67
  • 124