Automated grading with key or grading scale in Google Sheets is fairly easy to do. You need to create a table with a key and then specify the range where to “look up” the grades.
The corresponding function is vlookup
=VLOOKUP(G2;$I$2:$J$7;2;TRUE)
The beauty of this setup is its customizability. If you need to change the key or grade values (e.g. numerical values instead of letters) you can do just just by typing over the values. In case you need more than six values in the scale you also need to adjust the range in the vlookup formula.
Moreover, the setup still works if you want to delete or add columns for more tasks or categories. Here is a template which you can copy into your own Google Drive and modify.
One final tip: you can highlight top grades and fail grades with conditional formatting.
.png)
