match - Yagisanatode

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.

List of 7 grades for each student - Google Sheets

Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”

Google Sheets: INDEX, MATCH, VLOOKUP

Ever been faced with an issue where you can’t move your column to the far left in order to Vlookup? I just came across this issue recently.

The Problem – Vlookup

I needed to add teachers to a list of students who had to move into their class for two tests because their teacher (Hanna John-Kamen) would be absent on the test day.

Sheet: Hannah 302-15. Students split into other class sections.

Essentially, I was splitting the students into the remainder of the classes for that session time. In this student list sheet (Hannah 302-15) I had allocated the students new rooms and each room had a new teacher.

I couldn’t change the teacher proctors test list sheet ( Q2U1 Teacher) because the teachers who reference it would get confused. Unfortunately, the teacher’s name I wanted was in column B and the reference (The Speaking classroom) was in column I.

Just like Zoolander, I can't Vlookup Left — Sheet: Q2U1 Teacher. Just like Zoolander, I can’t Vlookup Left

I need to put my first Vlookup in M2 of the Hannah 302-15 sheet. If I tried to Vlookup it might look like this: =VLOOKUP(L2,'Q2U1 Teacher'!B:I,-8, False). Where L2 is the class I am searching for in the Hannah 302-15 sheet. ‘Q2U1 Teacher’!B:I is the range where B is the name column and I is the Class number. –8 is clearly wrong. Vlookup will not accept a negative reference (It’s stupid, I know). False: because the list was not sorted.