Filtering on gathered statistics adds dummy empty rows and fails anyway

Question

I have a simple data set:

Pupil A Teacher A   pass
Pupil B Teacher A   fail
Pupil B Teacher B   pass

And want to compute the following statistics:

Teacher     pass rate
Teacher A    50%
Teacher B   100%

The way I went about it is to create two columns with formulas, =unique(B:B) and =COUNTIFS(C:C,"pass", B:B, E2) / COUNTIF(B:B, E2).

Now, this works correctly but when creating a filter and ordering by pass rate, I got this:

Two-part question:

1. What am I missing? How to fix it?
2. Is there a smarter and a better way to do this?

Answer 1

The issue you're having the with the formula you're trying is less with the formulas and more with the Filter you've applied afterwards. for those Filters to work, you need to have "static" data. at least one column of it anyway. Whereas all your data is being supplied by formulas. A better way to get at what you're going for (i think) is this query, which i paced on your sheet in cell D1. Note that it sorts by the pass rate, (Z->A)

=ARRAYFORMULA(QUERY({Data!B:B,N(Data!C:C="pass")},"select Col1,AVG(Col2) where Col1<>'' group by Col1 order by AVG(Col2) desc label Col1'Teacher',AVG(Col2)'Pass Rate'",0))

Answer 2

You could also try one of the following 2 solutions offered as well:

Solution 01

(using a check-box)

=IF(B2=TRUE,SORT(QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null"),2,1),
                 QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null"))

Solution 02

(using a dropdown in data validation)

=IFERROR(QUERY({unique(Data!B2:B),ArrayFormula(IF(unique( Data!B2:B)<>"",COUNTIFS(Data!C2:C,"pass", Data!B2:B, unique(Data!B2:B)) / COUNTIF(Data!B2:B, unique(Data!B2:B)),""))}, "where Col2 is not null order by "&G2&" format Col2 '#%' "), "please make your selection in G2")

(Both the check-box as well as the dropdown could be placed anywhere on the sheet)

Here is a DEMO SHEET to test it out.