Formula to average Letter grades

[Pinkelephants2020]

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. 

[#2]

@Pinkelephants2020 

In I2:

=SUMPRODUCT($L$8:$L$19*(B2:H2=$K$8:$K$19))/COUNTA(B2:H2)

This can be filled down.

[#3]

@Hans Vogelaar 

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 :)

[#4]

@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.

[#5]

for Excel 2016 formula =CHAR(CEILING(SUMPRODUCT(--(CODE(UPPER(B2:H2))))/COUNTA(B2:H2);1))

[#6]

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.

[#7]

Auto correction option path is

go to file-option-proofing-Auto Correct Option

[#8]

=+SUMPRODUCT((B2:H2=K8:K19)*L8:L19)