Filtering on gathered statistics adds dummy empty rows and fails anyway

Posted on

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common formula parse errors in Google Sheets, like Filtering on gathered statistics adds dummy empty rows and fails anyway, and how to fix it.

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?

Answer :

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

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.

Leave a Reply

Your email address will not be published.