Hell's Bells! Not that question again! (and formulas for assigning grades)

During any given week at least half a dozen ideas for excellent blog topics flash through my mind, and that is generally the end of the matter right there.  Recently I came across a post on someone else's blog that was so good and so similar to my own thoughts on the subject that I decided I had to do something.  So I stole it.  No!  I am just "borrowing" the most relevant portions for this entry.  The original blogger is Dave Richeson, a math professor at Dickinson, and here is the link to his original post.

Exams in my introductory Geology course are much harder than the exams that students prepare for, especially that first midterm.  Handing these back to the class always brings about a predictable chorus of the same old question: "Are you going to curve these?"  To which I always ask "What does that mean?"

Do they really want me to apply a formula to everyone's grades such that the distribution falls on a bell-shaped curve?  Because that is how I have always understood the meaning of a curve.  I don't think this is really what they want me to do, so I tell them what I always tell them, which is: "At the end of the semester I adjust the scale when assigning final letter grades, but until then they will just have to live with a straight percentage."

So, now we get to the blog post that I stumbled upon.  Here is the section in which Dave discusses my exact thoughts on fitting scores to a bell curve:

7. Bell curve

What is it?  Here’s the way I understand the “bell curve”: make the mean a C, then the mean plus/minus a half standard deviation would be the C-/C/C+ scores, one more standard deviation out would give the B’s and D’s, and the tails would give the A’s and F’s. This could be tweaked in any number of ways—change the mean, fatten or slim the distribution.

I don’t know if this is used by any professors anymore (in small classes, at least).

Pros: grades end up with a very predictable distribution

Cons: ruthless, students competing against classmates

Use when: for standardized tests in which only a certain number of students can pass, for large classes or multiple sections when there must be a fixed distribution

As you can tell, Dave's post contains quite a list of possible curving techniques - at least seven.  Actually there are ten, but some of them are tongue-in-cheek, which is another reason to click on the link to read the original.

Now, if you ever wished that someone could show you how to automatically assign letter grades based upon raw percentage scores in Excel or a Google Docs spreadsheet, then this is the part you have been waiting for.  Dave does so in the section of his post on assigning letter grades.  Here is that section:

How to assign letter grades

I don’t like letter grades. I only use them at the end of the semester when I have to submit my final grades. What good are they in the middle of the semester? How do you average a B-, an A, and a B+?

This is the procedure I use at the end of the semester.

1. Decide on a fixed scale—i.e., how to translate percentage grades to letter grades. There does not appear to be a standard for how to do this. Here are two examples—one for straight letter grades and one including +/- grades (my college does not have an A+, but I included it because some schools do).

Percent (min)    Grade                    Percent (min)    Grade

0                         F                            0                     F

60                      D                           60                     D-

70                      C                           63.3                  D

80                      B                           66.7                  D+

90                      A                           70                     C-

                                                       73.3                   C

                                                       76.7                   C+

                                                       80                      B-

                                                       83.3                   B

                                                       86.7                   B+

                                                       90                      A-

                                                       93.3                   A

                                                       96.7                   A+

2. Quickly go through and assign letter grades using this scale.

If you are using Excel you can use this function to assign the grades automatically (if the percent grade is in column A):

=LOOKUP(A1,{0,"F";60,"D";70,"C";80,"B";90,"A"})

=LOOKUP(A1,{0,"F";60,"D-";63.3,"D";66.7,"D+";70,"C-";73.3,"C";76.7,"C+";80,"B-";83.3,"B";86.7,"B+";90,"A-";93.3,"A";96.7,"A+"})

If you are using Google Docs you can use this combination of functions:

=INDEX(FILTER({"A";"B";"C";"D";"F"};A1>= {90;80;70;60;0});1;1)

=INDEX(FILTER({"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F"};A1>={96.7;93.3;90;86.7;83.3;80;76.7;73.3;70;66.7;63.3;60;0});1;1)

There is more to this section, in which Dave gives his thoughts on tweaking final grades and on what to do about failing students; both are worth reading.  For now, though, let me explain those Excel and Google Docs functions a bit more.  All the functions assume that the percentage grades you want to assign letter grades to are in column A, with the first student's percentage grade in topmost cell, A1.  The functions as written reference this cell when performing their calculations, so you just need to copy and paste one of the functions above into a blank cell at the top of another column in your spreadsheet.  To perform the function on the rest of your students' percentage grades, simply copy this function down the column into as many rows as there are students in your course.  Of course, if your percentage grades are in a different cell than A1, you must replace "A1" in the function with the correct cell designation first.

For my purposes, since my preferred way to apply a curve is to adjust the final grading scale, I also would need to change the numbers, in whichever of the functions I choose, to reflect the percentage points at which I will make my letter grade splits.

Finally, a caveat.  You should be aware that copying and pasting any of the functions above may not work if there is a forced line break in the function.  You may need to paste the function you want to use into a simple text editor and get rid of the line break(s), then copy and paste from there into your Excel or Google Docs spreadsheet.

Thanks for reading.

Here again is the link to Professor Richeson's original post: http://divisbyzero.com/2008/12/22/how-to-curve-an-exam-and-assign-grades/