min - Yagisanatode

Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow.

In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:

FLITER
ARRAYFORMULA
MIN and MAX
IFERROR
SPARKLINE
UNIQUE
WEEKDAY
LEFT

We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.

However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:

How I came to do things in a certain way.
What I tested before applying to my Google Sheet.
The order I did things to create the test.

When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.

I hope you enjoy the following project…

Continue reading “Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1”

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.