0

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?

Improve this question
0

2 Answers 2

Reset to default
1

you can get the same result with this calculation, extended to all student rows and easier to understand:

=if(A2:A = "" ,"",sum(switch(B2:I2,"Good",2,"Mediocre",1,0)))
Improve this answer
2
  • Indeed it is much simpler, and I think I do not even need the first "IF" (I can simply copy this formula sum(switch(B2:I2,"Good",2,"Mediocre",1,0)) to all student rows).
    – Erel Segal-Halevi
    Commented Jan 30, 2022 at 10:55
  • eliminating the IF, the extension of the calculation to one (or more) row with an empty student name would produce a sequence of useless zeros
    – Daniele
    Commented Jan 30, 2022 at 11:01
2

Try this out in J2

=index(if(A2:A="",,mmult(--switch(B2:I,"Good",2,"Mediocre",1,),sequence(columns(B2:I))^0)))
Improve this answer
1
  • 1
    This works like a charm :-)
    – Erel Segal-Halevi
    Commented Jan 30, 2022 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.