Forum Discussion

Pinkelephants2020's avatar
Pinkelephants2020
Copper Contributor
Oct 22, 2020

Formula to average Letter grades

Hi All, 

 

I am not an expert in excel but I a, trying work out how I can calculate an average of grades. There are 7 questions each scored with a grade and then I need to average the scores. I added a simple sample if you could advise the best way to work out. I dont mind percentage or number as the result. 

Thanks in advance if you can help meantime I will keep trying. 

Formulas and Functions

7 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 22, 2020
    for Excel 2016 formula =CHAR(CEILING(SUMPRODUCT(--(CODE(UPPER(B2:H2))))/COUNTA(B2:H2);1))
    • devyadav2008's avatar
      devyadav2008
      Brass Contributor
      Dec 03, 2020
      Auto correction option path is

      go to file-option-proofing-Auto Correct Option
    • devyadav2008's avatar
      devyadav2008
      Brass Contributor
      Dec 03, 2020
      you can do this without any formula also..

      once you create auto proofing like whenever you write any grade it will be convert in score than you can sum.
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 22, 2020

    Pinkelephants2020 

     

    It is helpful to know the operating system and Excel version, as different approaches may be required depending on the version and OS.

     

    Formula for Excel 2019 and Excel for Office 365:

    =SWITCH(MID(A2,1,1),"A",4,"B",3,"C",2,"D",1,"F",0)

    NOTE: This formula will not work in older versions of Excel because it uses the new SWITCH function.

     

    How to Convert a Letter Grade to GPA points in newer versions of Excel

    https://www.techonthenet.com/excel/formulas/convert_letter_grade_to_gpa.php

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • Pinkelephants2020's avatar
      Pinkelephants2020
      Copper Contributor
      Oct 22, 2020

      HansVogelaar 

      Hi Hans, This didn't seem to work. Perhaps I didn't explain myself though. I tried an array formula in the total column  to average out the scores given to John, so B2 to H2 with the average showing in I2. This only works if you have letters not an A+ or B-, it wont recognise the minus or plus symbols. 

      I then thought I could create a list and give each letter a score/number and use this to create a formula. That is as far as I got as I don't know how to use the list to create the formula I need. 

      Thanks for helping me though. I am still trying 🙂

Resources