Get average of multiple columns-CodePudding

I have a table like the picture below and I would like

to get the average of every column of the table using a query
to sort all the average of every column of the table and keep only the 3 maximum (using a query)

Below is what I tried :

the following query only gives me the average of the first column =QUERY({17:22};"SELECT AVG(Col1) WHERE Col1<>0";1)
for sorting, I guess I will have to use ORDER BY something DESC ? And for limiting values, I guess I will have to use LIMIT 3, isn't it ?

Thanks for your help

CodePudding user response：

use:

=TRANSPOSE(SORTN(TRANSPOSE({17:17; BYCOL(18:22; 
 LAMBDA(x; IFERROR(AVERAGE(x))))}); 3; 1; 2; 0))

update:

=ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(QUERY({17:22}, 
 "select "&TEXTJOIN(",", 1, IF(17:17<>"", 
 "avg(Col"&SEQUENCE(1, COLUMNS(17:17))&")", ))&
 " where "&TEXTJOIN(" or ", 1, IF(17:17<>"", 
 "Col"&SEQUENCE(1, COLUMNS(17:17))&"<>0", )), 1)), 
 "order by Col2 desc limit 3", 0)))

CodePudding user response：

You can't query an average across multiple columns to my knowledge while excluding zero's. Therefore you cannot order the results and limit it to 3.

However if you are willing to accept zero's then this is a query that would work:

=TRANSPOSE(QUERY(TRANSPOSE(QUERY({A17:I},"SELECT "&ARRAYFORMULA("AVG(Col"&JOIN(",AVG(Col",SEQUENCE(COLUMNS(A:I),1)&")"))&"")),"SELECT * ORDER BY Col2 DESC LIMIT 3"))

UPDATE:

A super messy solution is to just compile arrays, not very dynamic and requires attention if more days are added:

=TRANSPOSE(QUERY({TRANSPOSE(A17:I17),{AVERAGEIF(A18:A,">0");AVERAGEIF(B18:B,">0");AVERAGEIF(C18:C,">0");AVERAGEIF(D18:D,">0");AVERAGEIF(E18:E,">0");AVERAGEIF(F18:F,">0");AVERAGEIF(G18:G,">0");AVERAGEIF(H18:H,">0");AVERAGEIF(I18:I,">0")}},"SELECT * ORDER BY Col2 DESC LIMIT 3"))