I love spreadsheets. I believe the spreadsheet represents the most innovative and amazing user interface ever created. Imagine explaining to someone how a spreadsheet works assuming they had never seen it before. They would absolutely laugh at you and say that is unintuitive and difficult to use. Yet it is used for millions of different tasks easily by even a novice user. A miracle of design.
I recently used it to convert letter grades into number grade point averages. (GPA) I am a fan of using letter grades to describe things. When we are recruiting, the feedback form has a letter grade input. One rule is that we reject anyone with a B+ or worse GPA.
Setting up a proper letter-to-number conversion in a spreadsheet was actually quite difficult and it took me a while to perfect it. There were many iterations with VERY lengthy formulas.
Here is a copy of the one I am using now.
The use case I used it for was a CoronaVirus Movie Club with my team. Each week one of the designers would declare the movie. We would watch it during the week and then have lunch together and talk about what we thought. We would keep track of what grades we gave the movie and see which ones we liked the best as a team.
Apparently, I am a tough grader. I had the worst GPA for all movies. Who knew I was this picky??
So I duplicated the sheet and converted it into a school GPA for a high-schooler. The formulas will work for other use cases. Feel free to copy it and use it for your own needs.
It would have saved me alot of time if I had found this a few years ago. If you have questions about the form
Whatya think?