Google Sheets QUERY statement: order by average

1
votes

I have this Google Sheet with a query formula in it. I am trying to use the query to display an average 1-5 satisfaction rating for each program. This script I adapted from Info Inspired - see "formula 1" - is basically working using the following formula in cell A2:

=query(ArrayFormula('Form Responses 1'!B2:E),"Select B,Avg (E) group by B label Avg(E)''")

However, this is displaying the results ordered alphabetically by program.

spreadsheet showing rating per program

How can I order the output by the average rating currently in column B of the image?

I tried using the order by clause as in:

=query(ArrayFormula('Form Responses 1'!B2:F),"Select B,Avg (F) group by B label Avg(F)'' order by Avg(F)")

and

=query(ArrayFormula('Form Responses 1'!B2:F),"Select B,Avg (F) group by B label Avg(F) order by Avg(F)''")

but both gave me:

Error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "order" "order "" at line 1, column 44. Was expecting one of: "format" ... "options" ... "," ...

I am not sure what the '' is doing, but the first query mentioned only works with it in there.

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

1 Answers

2
votes

try it like this:

=QUERY(QUERY(ARRAYFORMULA('Form Responses 1'!B2:E), 
 "select B,Avg(E) group by B label Avg(E)''", 0), "order by Col2 asc", 0)

'' is literally nothing eg. empty label

0