Google-sheets – Using a FILTER function in conjunction with other functions

formulasgoogle sheets

I am looking for a function to display the Title (from column B) that has the lowest numerical average in column (found from column G). I have that already in the form of

=INDEX(B2:G,match(min(G2:G),G2:G,FALSE),1)

but I would like to further specify it so that it only grabs the Title with the lowest average, that is also of a certain Category. So let's say I'd want a function to search for the Title with the lowest average only in Category A, I would want that to display "B" instead of "K", which the above function outputs.

Best Answer

Do unmerge I2:I. And fill all corresponding rows with category name to use FILTER.

Formula

=QUERY(
   IMPORTRANGE(
     "0Anrm0HgYyzzRdGR3TWpBelFCenJYM01ZUDZsaWNsTkE", 
     "Sheet1!C3:C13"
   ),
   "SELECT Col1 WHERE Col1 <> ''"
 )

Copy/paste
=QUERY(IMPORTRANGE("0Anrm0HgYyzzRdGR3TWpBelFCenJYM01ZUDZsaWNsTkE", "Sheet1!C3:C13"),"SELECT Col1 WHERE Col1 <> ''")

Explained

The QUERY formula will take the dataset obtained from the IMPORTRANGE formula and show the first column (Col1) only where Col1 has an entry.

Example

I've added my solution into your file.

Limitations

QUERY would try to determine character/numeric type of each column. If you have columns that have mixed text and number in cells, with numbers at start, it would be decided as numeric, and text cells replaced with blanks.

Google Sheets – Calculate Conditional Averages with Functions

Short answer

Instead of AVERAGEIF use an array formula as AVERAGEIF requires a range as it's third parameter.

Explanation

The below formula could be used to calculate the average for each category without having to use an auxiliary column and as it use open ended references, it will not require to be modified when new form responses be submitted.

=ArrayFormula(
  QUERY(
    FILTER(
      {'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C},LEN('Sheet1'!A:A)
    ),
    "select Col1,AVG(Col2) group by Col1")
 )

Using the example source data provided by the OP, the result is the following:

+---+---+--------------+
|   | A |      B       |
+---+---+--------------+
| 1 |   | avg          |
| 2 | A | 0.3333333333 |
| 3 | B | 0.75         |
| 4 | C | 0.8333333333 |
| 5 | D | 0.875        |
| 6 | E | 0.7569444444 |
+---+---+--------------+

{'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C} creates an array with two columns, the first one is the category column, the second calculates the dividend of Column B divided by Column C.
The FILTER function is used to remove blank rows.
The QUERY function is used to calculate the average for each category in the first column.

Best Answer

Related Solutions

Google Sheets – Using Combined IF THEN with IMPORTRANGE Formula