What is the average overall letter grade?

  • I have a list of letter grades of students. I need to determine the average overall letter grade in the list. What would the formula look like?

    For example: S2:S23 has the grades B-, F, A, C+, and so on. What would the formula in S24 look like to give me what the average letter grade of that list?

  • Hi DrRandyman,


    One way could be to create a reference table where every possible letter grade has a corresponding integer value (i.e. score). You can then use VLOOKUP formula to retrieve the score for each grade in S2:S23 and then average them. Since the average result could be non-integer, you would need to round it. Final step is to take the rounded score and reference the matching letter grade (using INDEX and MATCH formula).


    I have attached an example of how this could be done.


    In the case that every grade has equal weighting as each other (e.g. getting grade B is mildly worse than grade A and getting grade C is also equally mildly worse than grade B), then for a scale containing a total of 14 letter grades, the numerical values would be 1 to 14.


    Hope this sounds clear.


    Best regards,

    Roy

  • Asking for assistance with homework is perfectly fine. However, asking for the complete solution is not advisable because you don't

    learn anything when the entire answer is given.


    Create as much of a solution as you are able with your current level of understanding. Then, present that information with an example

    workbook in your request for assistance. You can let others know this is your homework ... that will help the volunteers to model their

    answers in a way that gets you to think through the challenge and arrive at an answer ... rather than simply give you the entire answer.


    Best wishes !