How to Write a Grade Roster

How to Make Your Own Grading Roster

Introduction

OK, you've used the rosters on this website and you want to make your own roster, using Microsoft Excel or some other database program. That's good. A custom-built roster can have any class size or number of assignments you'd like it to have. You can use it to do other things as well, like record the number of absences a student has, assign extra credit, make comments, and so forth.

This web page will not walk you through every step you'll need to take to build a roster, but it will point out some of the tricks. You'll need to have some basic familiarity with whatever database program you are using. If you want to use the rosters available through this website as sources, you should know that, although most of the data fields in them are protected (so that users can't accidentally overwrite them) and although some of the important rows are "hidden" (you'll be able to see which these are if you look at the row numbers), you can unprotect and unhide everything without a password.

Grading Systems

There are two main grading systems that people use: the percentage system and the letter grade system. The percentage system, which gets used often in the hard sciences, works pretty well. Assignments are scored in numerical form (0 to 100) and students know what those numbers mean. They also know how to calculate their final grades using those numbers. Better still, the percentage system is easy for teachers to use. If you plan to use the percentage system all of the time for your own grading, you won't need any of the rosters on this site and you won't need my tutorial on how to write your own spreadsheet.

But despite the ease of calculating grades with the percentage system, many teachers, mostly in the humanities and social sciences, still assign letter grades rather than numerical grades. They may feel that the sorts of assignments they give out cannot be scored with the precision of a 100-point scale and that it would be intellectually dishonest to try to do so (what makes an essay an 88 rather than an 89, anyway?). They may feel more comfortable with qualitative rather than quantitative forms of scoring assignments. They may find that the 100-point scale encourages students to grub for points rather than focusing on the larger tasks at hand. There are also some technical differences between the letter grade and percentage systems in terms of how failing assignments get graded--you can see the details here but the main point is that the letter grade system punishes failure less harshly.

The trouble with letter grades is that there is no uniform standard about how they translate into numbers. And you need to translate them into numbers if you want to average grades together (to calculate final grades after a number of differently weighted assignments, or to get class averages). The main way that gets used to translate letters into numbers is the four-point scale. Under this system, an A is a 4.0, a B is a 3.0, and so on down to F, which counts as 0. I will recommend, instead, a modified version that, although strange, is easier to understand and to use. It is basically the four-point scale multiplied by three:

Letter	Points
A+	13
A	12
A-	11
B+	10
B	9
B-	8
C+	7
C	6
C-	5
D+	4
D	3
D-	2
F	0

This system has three main virtues: (1) all letter grades have integer values, which makes reading and rounding grades much easier; (2) every "straight" grade (A, B, C, as opposed to A-, B+, C-) is a multiple of three, making it easy to remember; and (3) split grades are not complicated--an A/A-, for example, is just an 11.5. Although this thirteen-point scale is unfamiliar to most people, I find that using it for a single semester is enough to make it feel natural.

Averaging Grades

Once we've got a letters-to-numbers system in place, things are pretty easy. The least complicated way to set up a database is to enter every grade as a number in the thirteen-point scale. So, a student who gets an A- on a midterm, a B on a paper, and a B+ on a final, will have scores of 11, 9, and 10, respectively. If those grades were all weighted equally, it would be very easy to get the student's final score, which is the average of those grades. Just add up the numbers (11, 9, and 10) and divide by three--the average is 10, a B+. On a spreadsheet, the way to do this would be to enter each grade into a separate cell and then use some other cell to record the average of them using an averaging function. In Excel, it would look like this:

Midterm	Paper	Final	Average
11	9	10	=AVERAGE(asgnmt1,asgnmt2,asgnmt3)

In the place of "asgnmt1", "asgnmt2", and "asgnmt3", you would have to type the names of the cells, e.g., A1, B1, and C1, or C20, C21, and C22. The text "=AVERAGE(asgnmt1,asgnmt2,asgnmt3)" is what you would type in the cell where you want the average to appear, but in Microsoft Excel it would not display that formula. Rather, the cell would just show the number you were looking for, in this case, it would show the number 10 (because 10 is the average of 11, 9, and 10).

To get a weighted average (for example, if the grades did not all count the same), you will have to add in the percentage of each assignment of the final grade. Then, instead of just taking the average, as was done in the previous example, you would have to multiply each assignment score by its percentage and add them all together, like this: (Assignment1 * Percentage1) + (Assignment2 * Percentage2) + Assignment3 + Percentage3). In Excel, that would look something like this:

Midterm	Paper	Final	Average
25%	30%	45%
11	9	10	=SUM(asgnmt1prc1,asgnmt2prc2,asgnmt3*prc3)

As before instead of "asgnmt1" and "prc1" and the rest, you'd type in the names of the cells where those values appear (A1, B3, C6, whatever). Here, the final score comes out to a 9.95, which, if you round up, is a 10, a B+. You can even have Excel do the rounding for you using the =ROUND function.

Using the methods outline above, you should be able to make up a basic roster. It might look, in Excel, like this:

	Midterm	Paper	Final	Average
	25%	30%	45%
Jim	11	9	10	=SUM(jim1prc1,jim2prc2,jim3*prc3)
Bob	3	5	6.5	=SUM(bob1prc1,bob2prc2,bob3*prc3)
Sue	12	10	11	=SUM(sue1prc1,sue2prc2,sue3*prc3)
Average	=AVERAGE(jim1,bob1,sue1)	=AVERAGE(jim2,bob2,sue2)	=AVERAGE(jim3,bob3,sue3)	=SUM(avg1prc1,avg2prc2,avg3*prc3)

Turn Percentage Scores into 13-Point Scale Numbers

Let's say, making things a little more complicated, that you are going to be giving some quizzes, which are scored out of 100, and some papers, which are given letter grades. You want to put it all into the same system so that the values will play well with each other (i.e., so that you can average them all together). The mathematical formula for translating 100-point grades into 13-point grades is:

.3x - 16.5 = y

So, if you have a quiz score of 75 and you want to translate it into the 13-point scale, you plug in 75 for x:

(.3 * 75) - 16.5 = 6

That makes sense--if you get a 75 on a test you've earned a C, and in the thirteen-point scale, a 6 is a C.

One caveat: The equation does not work for scores below 55. Why? Because if you enter in a quiz score below 55, you'll end up with a negative number on the 13-point scale. As mentioned above, the grade letter system (on which the 13-point scale is based) and the percentage system (the 100-point scale) treat low grades different. This problem with the equation is simply a mathematical expression of that fact. The way to deal with it is to use the equation for any quiz score that is 55 or above. Any score that is below 55 should just count as 0.

Want to know how to do all of this in Excel? Of course you do. Here's how to do it.

Quiz Score (100-pt scale)	Grade in 13-pt scale
75	6
quiz1	=IF(quiz1<55,0,SUM(.3*quiz1,-16.5))

Translated from Excel-speak into English, that bottom-left box says: "If the value of quiz1 is less than 55, the number you're looking for is 0. Otherwise, the number is .3 times the value of quiz1 minus 16.5." And, as before, instead of typing "quiz1," type the name of the cell (A2, C5, etc.) where that value appears.

Turn Letters into 13-Point Scale Numbers (the really complicated stuff)

Now a final trick: entering letter grades (A-, B+, F) in one cell and getting out 13-point scale numbers in another cell. You'll want to do this if you don't want to have to translate the letters into numbers in your head but just want to be able to write the letters directly into the spreadsheet. The function you will need to do this is a little complicated. In Excel, you write it like this:

Letter Grade	13-point Scale Number
A	12
lett1	=LOOKUP(TRIM(lett1),{"A","A-","A+","B","B-","B+","C","C-","C+","D","D-","D+","F"},{12,11,13,9,8,10,6,5,7,3,2,4,0,"Error"})

In English, the bottom-left cell says: "Take the value of lett1, take off any extraneous spaces (the TRIM function), and compare it to this list of letter grades (A, A-, A+, etc.). If lett1 matches one of those, then the number you're looking for is in the corresponding place on this list of numbers (12,11,13,etc.). If not, then the answer should be 'Error.'"

If you want to get really fancy, you can have a function that gives you a value on the 13-point scale no matter whether you enter in a letter grade (A, B, C+), a split grade (A-/A, B+/B), or a number on the 100-point scale (85, 76, 50). You might also want this function to do some other little stuff, like making sure that you don't enter in "E" or "G" as letter grades or values above 105 or below 0 as percentages. That function is even longer, but here it is if you want it:

=IF(AND(lett1>=55,lett1<=105),SUM(0.3*lett1,-16.5),IF(AND(lett1>=0,lett1<55),0,IF(OR(AND(lett1>-100000,lett1<0),AND(lett1>105,lett1<100000)),"Error",LOOKUP(TRIM(lett1),{"A","A-","A-/A","A/A-","A/A+","A-/B+","A+","A+/A","B","B-","B-/B","B/B-","B/B+","B-/C+","B+","B+/A-","B+/B","C","C-","C-/C","C/C-","C/C+","C-/D+","C+","C+/B-","C+/C","D","D-","D-/D","D/D-","D/D+","D-/F+","D+","D+/C-","D+/D","E","F","G"},{12,11,11.5,11.5,12.5,10.5,13,12.5,9,8,8.5,8.5,9.5,7.5,10,10.5,9.5,6,5,5.5,5.5,6.5,4.5,7,7.5,6.5,3,2,2.5,2.5,3.5,1.5,4,4.5,3.5,"Error",0,"Error"}))))

In English: "Take the value of lett1. Is it a number between 55 and 105? If so, the number you're looking for is .3 times lett1 minus 16.5. If not, is lett1 a number between 0 and 55? If that's the case, then the number you're looking for is 0. Now let's imagine that lett1 is a number but it is not between 0 and 105. Then the answer should be "Error." If none of those conditions hold (i.e., if lett1 is not a number), then let's treat it like a letter. Take off any extraneous spaces from lett1 and then compare it to this list of letter grades (A, A-, A-/A, etc.). Does it match any of them? If yes, the number you are looking for is in the corresponding place in this list of numbers (12, 11, 11.5, etc.). What if lett1 is a letter grade but it doesn't start with A, B, C, D, or F? Then either starts with E, in which case the answer is "Error" or it starts with a letter that is later than F in the alphabet (G or later), in which case the answer is also "Error." Finally, if lett1 is neither a number nor a letter, the answer is "Error"."

And, we're done here.

Downloads
- Roster for classes of 30 or less, Excel 2007.
- Roster for classes of 80 or less, Excel 2007.
- Roster for classes of 30 or less, Excel 97-2003.
- Roster for classes of 80 or less, Excel 97-2003.

Other stuff
- Teachers using any of the rosters on this site can send students to this website, which allows the students to calculate their individual grades.
- Want to skip the download and just use an online roster? This roster accepts up to twenty students and this roster of the roster accepts up to sixty.
- This tutorial was written by Daniel Immerwahr.