I teach a college class that has letter grades for individual assignments.

I would like to take these letter grades, correspond these to a numerical grade (e.g., A=96) assign a percentage value to them (e.g., assignment 1 is 10% of overall grade) and then add all these to total the overall score for that student. (e.g., student Sam scores A for assignment 1, worth 10%, scores B for assignment 2, worth 15%, etc.)

  • What are the other grade possibilities and their numerical values ?? – Gary's Student Dec 24 '16 at 15:55
  • 2
    A simple approach would be a table of letter to number translations, and VLOOKUP to convert the values. Assuming each assignment is in a separate column, you could use the 1st row to hold the assignment weights. The overall score would be the sum of the individual assignment products: VLOOKUP translation * Weight. – fixer1234 Dec 24 '16 at 18:23
  • 1
    I'd change the way that I grade if I were you. Do all of your work in points, and convert to a letter grade at the end. All of weighting will just take care of itself. – picobit Dec 27 '16 at 18:24

There are free templates available for your specific needs. I would recommend using Teacher's Grade Book Template for Microsoft Excel® - by Jon Wittwer

This free Gradebook Template for Excel started out as a very basic grade book spreadsheet, but it has evolved into something that is very useful, flexible, and powerful (and still free). We now have a few different types of grade books that can handle most of the major grading systems used in high school and colleges.

It has the following features:

  • Weighting Factors vs. Points

  • Converting Letter Grades to Percentages

  • Extra Credit

There are 3 available templates to download. In your case I would use:
Gradebook Template : Percentage System

Just copy the Gradebook spreadsheet and name it as GradebookWithLetters or something similar and then put the data there. Then you can use the following function to match the letter grades to numerical values.

I cannot give you the modified template or share screenshots as it is against the License Agreement however I can help you modify your own template to match your needs.

Just put this in C11 in Gradebook (not the one with letters) and drag through the whole table. =IFNA(INDEX(Grades!$B$36:$B$49;MATCH(GradebookWithLetters!C11;Grades!$A$36:$A$49;0));"")

Then just replace the values in Points/Weighting with 10% = 10 and so on. (Do not use percentages there as it messes up the calculations)

The main page contains a lot of tips and explanations on how operate the template.

I've used this template to calculate grades for a family member of mine who is a teacher.

All the credit goes to the creator of the template.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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