Using Grades in Google Sheets

• Introduction
• Conversions
  • Numerical Conversions
  • Lookup-Free Conversions
  • On-the-Fly Conversion
• Back to Grades?

Using grades in a spreadsheet doesn’t have be difficult, here is how to do it elegantly.

Introduction

A lot of data that ends up in a sheet is discrete, or categorical. This means that it has a limited number of values. A good example of this type of data is grades, or levels. Numerical levels are normally easier to handle because you can natively perform mathematical or statistic calculations on them (e.g. find the average, or calculate the statistical deviation of a dataset). Discrete data represented by non-numerical symbols (e.g. letters, or letters and symbols) presents a few more difficulties - but these can be overcome.

Conversions

Almost all statistical, display or mathematical functions will operate on numbers only. This means we will have to do some conversion work on our grades before we can use them properly.

Numerical Conversions

Often the simplest way to convert between a text value (such as a grade) and a numerical equivalent is to use a lookup table, such as the one below.

By using a table like this somewhere in your sheet (it doesn’t need to be in the same tab) you can use vlookup or the more powerful index / match functions to directly convert grades to their numerical equivalents.

Using VLOOKUP

VLOOKUP(A1, Lookups!$A$1:$B$9, 2, FALSE)

VLOOKUP(A1, Grades, 2, FALSE)

The first example assumes that the lookup table is in a tab called Lookups. The second one uses a named range, which is an excellent way to make your life easier. Why? Because if you need to add or remove further grades to your lookup table in the future, you need only update the cells included in the named range, rather than all the formulas which refer to that range! If you do need to update a lookup table like this, without using a named range, insert a new row (or remove a row) from somewhere in the middle of the table, then move around your values. This will be more likely to correctly automatically update any references to the range!

Using INDEX / MATCH

INDEX(Lookups!$B$1:$B$9, MATCH(A1, Lookups!$A$1:$A$9, 0), 1)

Whilst a lookup table is a viable method, it can quickly get tiresome if you have many columns of data to convert, leading to sheets that look a lot like this:

As your sheet expands, so do these annoying extra columns! Whilst you can hide them visually, they make it much harder to create sparklines without resorting to techniques such as using arrays to aggregate columns.

Lookup-Free Conversions

It is possible to undertake numerical conversions without needing to rely on and maintain lookup tables. If you are concerned with the relative, rather than absolute, analysis you can use the range of the data in your dataset to generate its own lookup table. Relative analysis is concerned with looking at patterns or trends within a dataset (e.g. who did best, worst, what was the spread/deviation) rather than being used to compare values between different datasets. To use a dynamically generated lookup table for sorts, you need to think about how your grades will end up being alphabetically sorted. The following table contains some lists of grades that have been sorted using the sort function in sheets.

As you can see, symbols sort ‘higher’ than letters, but a letter followed by a symbol will sort below a letter on it’s own. This means it makes a lot of sense to use ‘*’ instead of A* if you have a ‘whole’ grade system. If you need to use a fine-grade system (with ‘high’ and ‘low’ variants of each grade) you don’t have many options to choose from if you would like to maintain ‘sortability’. Unfortunately, you can’t just use an ‘a+/a/a-‘ system, as the grade on it’s own will sort above the grade followed by a symbol. Even symbols themselves have a sort order, shown in the table below (upper- and lowercase versions of the same letter will sort the same, retaining the order in which they were passed to the sort).

Use a+ | a= | a~ for high | normal | low grades.

Probably the best solution is to use suffixes to designate a high (+), normal (=) and low (~) version of the grade. This will work because the sort order of these symbols is correct. You cannot use a dash / minus symbol to designate a low because it sorts relatively highly (just after an underscore). A tilda (~) sorts below an equals sign (=) which itself sorts below a plus (+). If you also need to use a symbol as a ‘top’ grade category, this system will also work. Asterisks (*) tend to be a bad idea, as they are commonly used as ‘wildcards’ for searching, and functions like match will return unexpected results if you use them. A better option is to use a dollar sign ($) instead. Grades can be used as either upper-case or lower-case (or a combination), as both will sort equally, which reduces the scope for accidental error. The following is a fine-grading scheme that sorts accurately in a spreadsheet.

Tabular

Sorted

Once we have a properly sortable grade system, we can use this to do lookups without resorting to a lookup table. This is done by creating a unique set of grades that have been used in our dataset (which is an array aggregating columns A, B, C & D in this example, with rows 2 to 30 containing data). The unique set of grades are then reverse sorted, and a match is performed. The value returned is a number indicating the position (in the array) where the value was found, which will be 1 for the lowest grade, and 18 (in our example) for the highest. This assumes that all the grades have been used, which is why this technique can only be used for relative analysis.

MATCH(A2, SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), 0)

On-the-Fly Conversion

Once we can convert our values without resorting to a fixed lookup table, we can also perform mathematical calculations on them, using the arrayformula function.

AVERAGE(ARRAYFORMULA(MATCH(A2:A30, SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), 0)))

The arrayformula function will essentially perform the inner match formula for each cell between A2 and A30 inclusively. The converted numerical results will then be passed to the average function, and their arithmetic mean returned. We can use the same technique to calculate a standard deviation (for the column of data), or even to generate a sparkline for the row.

STDEV(ARRAYFORMULA(MATCH(A2:A30, SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), 0)))

SPARKLINE(ARRAYFORMULA(MATCH(A2:D2, SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), 0)))

Using these techniques, you can visualise and analyse your data without needing to add in either lookup tables or extra columns. It is a powerful technique that can even be extended in custom conditional formatting as well (highlighting high / low outliers and performances).

Back to Grades?

You may well wish the convert the final numerical result (e.g. an average) back to a grade. This can be done with the help of the index function, which essentially is the reverse of the previously used match (e.g. it will return the grade at a position in our dynamic lookup table, rather than the position of a given grade).

INDEX(SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), ROUND(AVERAGE(ARRAYFORMULA(MATCH(A2:A30, SORT(UNIQUE({$A$2:$A30;$B$2:$B30;$C$2:$C30;$D$2:$D30}), 1, false), 0)))), 1)

Implementing all of this means that we end up with a sheet that looks a little like the table below, with simple value columns (filled in with grades), a numerical average, a numerical standard deviation, a grade average and a sparkline for each row. All without the need to maintain anything apart from remembering to use our sortable grade system!