0

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:

enter image description here

Two-part question:

  1. What am I missing? How to fix it?
  2. Is there a smarter and a better way to do this?
  • Could you share a(n Editable) sample sheet via link with some sample data? It'd be fairly simple to show you on a sheet, but difficult to demonstrate in the abstract – MattKing Feb 6 at 22:03
  • @MattKing sure, see docs.google.com/spreadsheets/d/… the result is correct, but clicking "sort Z -> A" on pass rate breaks it :/ – g_user Feb 6 at 23:28
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Feb 7 at 17:26
0

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))
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.