Search code examples
google-sheetsaveragegoogle-sheets-formulaarray-formulasgoogle-sheets-query

Google Sheets Column function to get Average of all the columns in a range to the right using current row

I'm not sure why I can't find examples of this. But I want my column E to have an average of all the columns to the right. In single example it's: =AVERAGE(F2:O2)

Now I try to do this for the whole column: =ArrayFormula(IF(ROW(E:E)=1,"Aggregate",AVERAGE(F:O)))

The problem is it doesn't do it per row, it just does the whole array. Maybe i don't want to use ArrayFormula

I should also put a IF(ISBLANK(F (Row())), "" So I don't calculate blank rows.

How do I put dynamic row on this?

Solution

  • try:

    =ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(F2:O), 
 "select "&TEXTJOIN(",", 1, 
 IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(F2:O),,99^99)))<>"", 
 "avg(Col"&ROW(F2:F)-ROW(F2)+1&")", ))&"")),
 "select Col2"))

    0