I would like to create a Google spreadsheet for grading students' homework. There are 8 rubrics (columns). In each rubric, there should be three levels: "good", "mediocre", "bad". They are worth, respectively, 2 points, 1 point and 0 points. The total grade is the sum of the grades in all 8 columns.
I found here an explanation of how to create the dropdown list for each rubric. I created a dropdown list that contains the three options: "good", "mediocre" or "bad" (see the example sheet). Now I need to write a formula for the final grade. One option is to create, for each column, an auxiliary column in which the text is converted to a number, e.g. using "If" or "Switch", and then sum the values in the 8 auxiliary columns. Is there a more efficient way to do this, without using extra columns?