Google Workspace and Software Development
Javascript, WordPress, Your Affiliate Program’s Campaign Link
When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so pleased to see a growing community in my comments sections.
Your support has really helped me to produce more and, I hope, produce better content. Thanks.
Just take me to the code!!!
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.
Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”
Google Sheets: FILTER, RANK
This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program.
Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally, copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss.
However, with 100 classes this was going to turn into a time-consuming chore and one that could be prone to errors.
To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.
|
1 |
=RANK.EQ(D2,FILTER(D:D,C:C = C2)) |
|
1 |
=RANK.EQ(Grade,FILTER(Grade Column, Group Column = Group)) |
Let’s move on to an example.
