Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF
In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment.
For lecturers with small course sizes, this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.
Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students. My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student.
I use Google Sheets for this purpose for its ease of use and sharability.
This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test.
Step 1 – Find The Lowest Grade with MIN (optional)
Our first step in the process is to find the lowest grade for each of the tests. To do this we use the MIN formula:
=MIN(range of values to search to find the minimum)
Step 2 – Record the Unit Where the Lowest Grade was Found (optional)
While this step has no purpose in the further progress of preparing a list of all grades except the lowest, it is important to be able to quickly reference the students lowest grade should I be called upon in future.
INDEX and MATCH will help me find the unit number in row 2 by comparing the value of the lowest grade in column J against the array of the student’s grades. Here’s the formula:
=INDEX(Units,MATCH(Lowest Grade,Grades for Student,FALSE-not ordered list))
So our first row would look like this:
Step 3 – Get New List of Grades Excluding the Lowest with FILTER and MIN
In our next step, we want to grab the student’s grades for the 7 units (col B:H), but filter out the lowest. We will create an array of the grades and filter them to exclude the minimum value of the array.
To do this we will use the FILTER formula that takes an array of values and filters them by the same array or another array of equal length based on a criteria. In our case, our filter criteria in the same array of grades and our criteria must not contain (<>) the MIN of those grades. Let’s take a look:
=FILTER(Grades,if Grades <> MIN(Grades))
So to filter out the lowest grade for our first student, it would look like this:
=FILTER(B3:H3,B3:H3 <> MIN(B3:H3))
Now you have your list of grades minus the lowest. We can now go ahead and get the total of the Grades for each user with SUM.
Wow! Wow! Wow! Just hold on a damn minute Yagi!!!! What if I have more than one lowest grade!!!! I’ll need to add at lease one of those to my total!
Okay! You got me. We need to take this into account. There are a number of approaches I used to solve this issue, but in the end this was the simplest approach:
Step 4 – Getting the Total of the New Set of Grades While Taking into Account that there might me Multiple Lowest Grades
You see, if there is more than one grade that is equally the lowest, then my FILTER based on the MIN will be less than the six grades I need for my course total.
Let’s say student Odi, has two equally lowest grades of 14 – one in Unit 4 and one in Unit 7. Let’s see what the result would look like:
We can see here that the filter is only producing 5 of our 6 needed tests. Likewise, Odi is missing 14 points towards his total. The FILTER is taking out all grades that equal the MINimum grade.
If Odi had minimum grades all with the value of 14 the remaining results would only have 4 grades displayed.
To fix this problem we are going to change our simple SUM formula in column R to something a little more complex. Before we SUM all of Odi’s remaining Unit Test grades we are going to:
- Count the number of empty cells from Column L to Q in the Remaining Unit Test Grades section for each students row.
- Multiply that number by the lowest grade.
- Add that to the sum of the grades in Column L to Q.
=(COUNTIF(Remaining Grades Cols,””)*MIN(All Grades))+SUM(Remaining Grades Cols)
In Odi’s row, the formula would look like this:
We’ll go ahead and change the grades to reflect some of the students who now have more than one minimum grade.
Now we have a proper total of our 6 highest grades, taking into account multiple minimum grades.
Here is a link to the sheet: Remove Lowest Grade