[UPDATE: An alternative method explained at bottom of post.]

A lot of us are furiously crunching numbers into Spreadsheets, but then we’ll have to convert those final grade percentages to letter grades.

Here are some formulas you can use to automatically generate a Final Letter Grade from your students Final Grade Percentage. Even if you can do the conversion quickly in your head, this method is probably much quicker and it reduces the likelihood of human error.

It’s VERY simple. Happy Grading and Happy Holidays!

Instructions

1. Tally up your students’ final grade percentage (on a 100 point scale).
2. Copy one of the functions below.
3. Paste the code in the cell where you want the first student’s letter grade to appear.
4. Important: Make sure you change all occurences of ‘A1’ in the function to the cell ID where the first student’s Final Grade Percentage is located.
5. Fill the Formula Down the Column
   (If you’re using a Spread Sheet Program – I’m assuming you know how to do this part.)

Standard Scale

Formula
=IF(A1>93,”A”,IF(A1>89,”A-“,IF(A1>86,”B+”,IF(A1>82,”B”,IF(A1>79,”B-“,IF(A1>76,”C+”,IF(A1>72,”C”,IF(A1>69,”C-“,IF(A1>66,”D+”,IF(A1>62,”D”,IF(A1>59,”D-“,”F”)))))))))))

Grade Scale
A = 94-100
A– = 90-93
B+ = 87-89
B = 83-86
B- = 80-82
C+ = 77-79
C = 73-76
C- = 70-72
D+ = 67-69
D = 63-66
D- = 60-62
F = 59 – below

Simple Scale (No Plus-Minus)

Formula
=IF(A1>89,"A",IF(A1>79,"B",IF(A1>69,"C",IF(A1>59,"D","F"))))

Grade Scale
A = 90-100
B = 80-89
C = 70-79
D = 60-69
F = 59-below

UPDATE: I sent this around to our faculty list serv. It was suggested to me that the VLOOKUP function would be simpler. This seems true, but it takes a bit more know-how to get it set up. However, if you have a template to work from – it’s easy. Roger Byrne, a professor in our biology department, sent around this this helpful template. Thanks, Roger!

7 Responses to “Percentages into Letter Grades: Spreadsheet Formulas”

1. 

   thom blake

   December 19, 2008 at 3:02 pm

   I’ve never cared much for averaging grades. I think I’ll have to write a blog post about it.

   Reply
2. 

   Dale

   December 20, 2008 at 10:50 am

   Comes out “Invalid Expression” in Gnumeric… 🙁

   Reply
3. 

   John Olsavsky

   December 20, 2008 at 6:07 pm

   I don’t think a LOOKUP function is any more difficult than nested IFs.

   =LOOKUP(A1,{0,”F”;59.5,”D-“;62.5,”D”;66.5,”D+”;69.5,”C-“;72.5,”C”;76.5,”C+”;79.5,”B-“;82.5,”B”;86.5,”B+”;89.5,”A-“;93.5,”A”})

   But, you say tomato….

   Reply
4. 

   Andrew Cullison

   December 20, 2008 at 7:37 pm

   Thanks John!

   LOOKUP is actually simpler. I wasn’t aware of that function. I was thinking about the VLOOKUP, that references a separate table in the spreadsheet.

   Reply
5. 

   Kevin Timpe

   December 22, 2008 at 10:28 am

   I tried John’s suggustion and got a formula error highlighting the “F”.

   Reply
6. 

   Andrew Cullison

   December 23, 2008 at 12:25 pm

   Kevin,
   Did the formula with the nested IFs work?

   Reply
7. 

   Ebenezer

   March 14, 2014 at 10:41 am

   Thank You very much Andrew Cullison. The formula worked for me. I appreciate you posting it for the benefit of everybody. It made my work simple.

   Thank you once again. God Bless you.

   Reply