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 it’s 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.
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.
Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.
Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.
Let’s look at the creation process through a recent example I created:
I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.
It goes a little like this. If I want to build a 5 digit random number I would:
But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?
Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.
For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.
Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.
You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.
Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.
Sometimes you have a need to reverse a list quickly in Google Sheets.
That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not in alphabetical or numeric order?
You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.
You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.
Let’s look at two workarounds that can help you out with this problem.