Letter-Only Values

[BArcher]

Hello,

I'm a school teacher and our district has just gone to letter-only grading. We get our knuckles wrapped if we use ANY numbers in grading student marks. What I am wondering if is there a way to use weightings and letter-only gradings to arrive at a value.

For example:
Student 1 gets an A worth 35%, a B worth 15% and a D worth 50%.

Is there a formula to get their overall result in Excel?

Thanks!

 

[Sektor]

SUM function?

 

[Rick Rothstein]

Weighted averages is the answer. First, though, you are going to have to decide on numerical equivalents for you grades and that may depend on whether you have plus and minus grades (such as A+, C-, etc.) as well as whole letter grades. Working with whole letter grades only, pick a numerical value for your A, B, C, etc. (I'll use A=1, B=2, C=3, etc.). Then, since your percents total 100%, you just need to multiply each number grade by its decimal value (percent) and add. For your example....

=1*0.35 + 2*0.15 + 4*0.50

which equates to 2.65. Now, to convert it back to a letter grade... 2.50 would be the break point between a B and a C. Since 2.65 is further away from 1 (an A) than 2.50 is, then the letter grade would be a C.

 

[BArcher]

Thanks for that! Is there a way I can get those numerical values to turn into letters?

 

[Biz]

Assuming your final weighted grade is in cell A1
Try formula below

=VLOOKUP(ROUND(A1,0),{1,"A";2,"B";3,"C"},2,1)

Does this help to answer your question?

Biz

 

[BArcher]

Thanks for that, Biz.

The marking scale we use mandates + and - grades. I just tried using the formula you suggested and it really doesn't like it. I've put into Excel the marking scale we're using with the minimum % grades we would have previously given. Is there a formula I can use to get Excel to look at that instead of typing it all in manually?

 

[BArcher]

Ok, just a quick update. I've been able to get it to get the numbers to turn into letters for the overall grade.

Is there a way I can get it to do this in reverse? So, I enter in a letter and it assigns a number to that letter and still completes the formula?

 

[Rick Rothstein]

You should have mentioned the plus/minus requirement when you responded to my message (where I brought them up as a possibility). Question... let's say you gave two tests where each was worth 50% of the grade and let's say the student got an A on the first test and a B on the second test... the student average is at the break point between the A and B, what does the student get... an A- or a B+ overall mark?

 

[BArcher]

They would get an A-. We have to give benefit of the doubt. Apologies for not mentioning it before - totally slipped my mind.

 

[BArcher]

Ok, I managed to work around it using an INDEX and MATCH formula. Thanks again for your help!