I have a PE teacher that wants to make her grading simple.

She has a 5 point system: 5 points for Attending on Time, Being Properly Dressed, and Participating. She wants to be able to put a letter in the cell for the apporiate score such as H=5 or (Absent) A=0, or (Tardy) T=4. Once a student has all their variables typed into the row we then want to get the sum of the variables. I am having trouble, as I am able to assign a value to a variable but when I enter that variable into the cell I see the number e.g. i type =H in the cell but 5 shows up. How can I do this so she can put letters down and get the sum afterwards?

  • Is this a homework assignment? Commented Feb 16, 2023 at 4:16
  • Are all the variables being entered into the same cell, or do you have a specific column for each one? (The latter will make this much easier, but the former can be done with RegEx) Commented Feb 17, 2023 at 14:56

2 Answers 2


Other thing you can do is to create a table with the values and their corresponding numbers. Then you can look up the values of the qualifications in each row, and SUM them.

In my example A1:B6 is my table of reference. And in A8 I have this formula for summing that row:


enter image description here


One way to do that would be to use named ranges. If you have a named cell H that contains 5 and a named cell T that contains 4, =H will get 5 and =H + T will get 9.

You can toggle the view between letters and through View > Show > Formulas.

  • Yes, but how can i have H show instead of 5 in the cell. For ultimately we want to put H in the cell and then sum the cells.
    – user308411
    Commented Feb 15, 2023 at 20:22
  • Edited the answer. Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Feb 15, 2023 at 20:39

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.