Array Formula with AverageIF of a row

0
votes

I have a formula in a Google Sheet that is averaging the values in a row...

=AVERAGEIF(A2:L2,"<>0")

Simple, right?

However, I cannot figure out how to get the ARRAYFORMULA to work for this.

=ARRAYFORMULA(AVERAGEIF(A2:L,"<>0")

That formula only averages all of the cells in the range, instead of returning the average for each row. What am I missing?

1
google-sheetsaveragegoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

1
votes

you were close. try:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:L),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A),
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&"")), 
 "select Col2"))

then to add <>0 you need:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:L),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A),
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&
 "where "&TEXTJOIN(" and ", 1, IF(LEN(A2:A), 
 "not Col"&ROW(A2:A)-ROW(A2)+1&"=0", ))&"")), 
 "select Col2"))

0