Using Spreadsheets to Keep Track of Students’ Grades

Richard Zach
v 1.2 – 14 November 2005
v 1.1 – 17 February 2001
v 1.0 – 5 November 1999

Introduction

Spreadsheet programs like Microsoft Excel and OpenOffice.org Calc can be used to manage grade sheets effectively. Not only do they provide a safe (multiple backups) and clean (printouts) way of keeping grade records, they can also be used to automated complicated tasks from calculating averages to providing detailed statistical analyses of the class’s performance. This brief guide deals with the basic and most common tasks involved. Basic familiarity with spreadsheets and productivity software in general is assumed.

A sample spreadsheet will be referred to below. From the “File” menu, select “Download as” to save it on your own computer.

Setting up the grade sheet

The first thing you’ll need to do is collect the information which you want to include in the grade sheet. Student names and ID numbers will be probably be the minimum you want to enter, but having other information included in the spreadsheet might be helpful sometimes. For instance, if you keep track of the entire class in one spreadsheet shared by all TAs, you might want to assign numbers to each section of the class. Then you can sort grades by section number, to make it easier for each TAs to see where their students are, and at the end sort by last name so final grades can easily be entered into the final grade report. Phone numbers and email addresses are convenient to have as well. You should have a separate column for each of these, so you can sort the list easily. Then make columns for each assignment (paper, midterm, final, and maybe a participation grade) that you want to track.

Once the spreadsheet is set up and grades are entered, you will want to do something with them. The most important task will be to calculate overall grades from the individual assignments you have entered. This can take the form of a simple average, to more complicated weighted averages, dropped lowest grades, and grading on a curve.

Converting letter grades to numbers

The main difficulty, however, will be to convert letter grades to numbers (which can then be averaged, etc.) and back. One way to do this is using a lookup table. Somewhere on your spreadsheet, below or to the right of the main area, enter in a vertical list of letter grades and the corresponding numerical values in the adjacent cells to the right (see (1) on the spreadsheet, cells B16:C28). It might be convenient to use standard grade points (A=4, B=3, C=2, D=1, F=0; for + add 0.3, for – subtract 0.3), but you can use any scale you want. Once you have this table, you can use the VLOOKUP function to calculate a numerical value from a grade point. Suppose cell B10 contains a letter grade. The corresponding grade point value is calculated by the following formula:

=VLOOKUP(B10, $B$16:$C$28, 2, FALSE)

This directs Excel to take the value of the cell B10 (e.g., “A”), look it up in the first column of the area $B$16:$C$28, and return the value in the 2nd column of that area (e.g., 4.0) as the value. If you plan to give out A+’s and want to take them into account for final grades, you should add a value above 4.0 for these. If you want to give “slash
grades” (B+/A-, etc), you will have to enter all of these as well, and come up with numbers. Make sure all GSIs agree on a policy for slash grades. For instance, some regard A-/B+ to be the same as B+/A-, some don’t.

The $’s in the specification of the lookup table make the reference absolute, that is, when you copy the contents of the cell where you entered the above formula, you can paste it anywhere else in the spreadsheet (i.e., next to the grades of the other 100 students in your class), and the reference will stay the same. If you enter the formula in the first row of your grade table, and then mark it and all cells beneath it, you can use the Fill Down function (in the Edit menu) to copy the formula for each student without having to change the references.

Note that for VLOOKUP to work correctly, the lookup table has to be sorted alphabetically (that means, “A” before “A-” before “A+”, etc). You can use the Sort function in the Tools menu to do that automatically.

Converting numbers to letter grades

A similar trick will let you convert grade points or percentile ranks back to letter grades. In the vicinity of your letter grade to grade point table, set up a second table. Here, the first column will contain cutoff values corresponding to the least number you think should qualify the student for the letter grade you enter in the adjacent cell. This table also has to be sorted in ascending order. The first value should always be 0.0, corresponding to F. The second row will contain the minimum score needed to earn a D-, and so forth.

You could just use the same table, sorted differently, as the one you use for converting letter grades to numbers, but I would advise against it. First of all, if you’re converting between letter grades and numbers, you will most likely be doing this because you average the grades on various assignments. Unless you are a really strict grader, however, you will probably not want to give student an A- if he or she has written 3 straight A papers and an A- paper. If you set the cutoff for an A at the grade point value of an A, i.e., 4.0, the average of 3 A’s and an A-, i.e., approximately 3.9, will not make the cut. A second reason is that you probably won’t want have to deal with students who complain because they missed the next higher grade by only a tenth of a grade point. In addition, manually setting the cutoff points allows you to curve the outcome slightly, and to account for natural breaks in the grade distribution. A good starting point would be 0.1 below the standard grade point value (3.9 for an A, 3.6 for an A-, 3.2 for a B+, etc.).

Once you have set up the second lookup table (say, at G16:H28), a numerical score at cell G10 can be converted to a letter grade using the formula

=VLOOKUP(G10, $G$16:$H$28, 2)

Calculating grades

With these two tricks, we have all we need to get started. The following table illustrates how a small grade sheet would take letter grades, convert them to grade points, average the scores to obtain an overall grade, and convert the result to a letter grade:

A B C D E F G H I J K
35 Name Paper 1 Paper 2 Paper 3 Final Paper 1 Paper 2 Paper 3 Final Average Course
36 Adams, John A A- B+ A- =VLOOKUP(B36, $B$16:$C$28, 2) =VLOOKUP(C36, $B$16:$C$28, 2) =AVERAGE(F36:I36) =VLOOKUP(J36, $G$16:$H$28,2)

The grades in columns A-E are entered by hand, the grade points in columns F-I are converted according to the first conversion table, the average is obtained by using the AVERAGE function, which is then converted to a letter grade using the second table. Of course, if all you are interested in is the final grade, you can do all this with a single complicated formula. Having separate columns for the grade points, however, will let you perform an analysis of your students’ performance per assignment.

Weighted grades

In most classes, the final grade is composed out of several assignment grades, and not all assignments are weighed equally. In this case, we add a weight to the conversion formulas in columns F-I and sum the values instead of taking the average.

A B C D E F G H I J K
43 15% 20% 30% 35%
44 Name Paper 1 Paper 2 Paper 3 Final Paper 1 Paper 2 Paper 3 Final Average Course
45 Adams, John A A- B+ A- =VLOOKUP(B45, $B$16:$C$28, 2) * F$44 =VLOOKUP(C45, $B$16:$C$28, 2) * G$44 =SUM(F45:I45) =VLOOKUP(J36, $G$16:$H$28, 2)

Keep the percentages in a separate cell rather than making them part of the forumula–this way they can be easily changed. Use the $ in front of the line number so you can copy and paste the formulas to the remaining rows in your spreadsheet.

Missing grades and averages

While you have all these grades in the computer, you might as well use them to analyze the performance of the students. What’s the average grade on the last paper? On the final? Are they distributed in a nice curve or are there lots of A’s and lots of C’s? Spreadsheets have lots of statistical functions, the three most commonly used ones are AVERAGE, MEDIAN, and STDEV. AVERAGE calculates the mean value of a data set. The MEDIAN of a data set is obtained by sorting the data set and then picking the value that falls in the middle (or the average of the middle pair, if the number is even). For instance, you might have lots of As and Bs, but a sizeable number of Ds and Fs. While the average could be a C, the median would probably be a B. The standard deviation, calculated by STDEV, is the mean distance of samples from the average. It is a measure of how flat or steep the curve is.

These functions make it easy in principle to calculate averages, however, there is a problem. Missing assignments should not figure into the calculation, since many empty cells actually correspond to people who have will already have dropped the course. Since we often don’t know that, we’ll have to keep them in the grade table. But if their assignments have 0’s in them, they’ll lower the average (significantly). But just as these grades can be simply left out of the letter grade chart (rather than be recorded as F’s), they can also be left out of the calculation of averages. The statistical functions AVERAGE, MEAN, and STDEV only take into account cells with numerical values in them, so all we have to do is to leave the grade point column of a missing assignment empty, instead of assigning 0. Incidentally, you’ll have to do something similar if you choose to leave missing assignments blank rather than report them as F’s, even if you don’t want averages: VLOOKUP doesn’t return a number if there is nothing to look up.

This can be accomplished by the formula

=IF(EMPTY(B57), “”, VLOOKUP(B57, $B$16:$C$28, 2, FALSE))

This returns an empty string, which will not be part of the average, if cell B57 is empty, and the grade point equivalent of a letter grade otherwise.

Average, median, standard deviation can then be calculated per column using the AVERAGE, MEDIAN, and STDEV functions. For counting the number of papers turned in, use the COUNT or COUNTA functions. Use COUNTA in the columns containing letter grades, and COUNT in the calculated columns. (COUNTA counts the number of non-empty cells; COUNT the number of cells with numerical values.)

Be aware that you cannot use the values so obtained to calculate a grade by straight average, since for the grade you do want missing assignments to count as 0. To convert empty cells to 0’s, use the N function:

=N(B57)

returns the value of B57, or 0 if B57 is empty. The best thing to do is probably to have two tables, the original data, and the data with missing entries converted to zero. (You can do this easily as follows: Pick the upper left corner cell of the area of your spreadsheet which you want to contain the copy with missing values replaced by 0. Enter “=N(cell)” as the formula, where cell is the address of the upper left cell in the original data array. Then mark an array of cells of the same width and height as the original array. In the Edit menu, find the Fill entry, and select Fill Right, then Fill Down. This will fill the entire array with formulas of the form “=N(cell)”, each cell referencing the corresponding cell in the original array.)

Dropping lowest grades

Some instructors like to drop the lowest paper grade for calculating the final. The SMALL function comes in handy here. SMALL(array; k) returns the k-th smallest value of the range of values in array. So suppose you have the three paper grades in cells F71:H71 and the final exam score in I71. To calculate the final score, where you drop the lowest paper grade and weigh papers at 60% (30% each) and the final at 40%, use the formula

=(SUM(F71:H71)-SMALL(F71:H71))*.6/2+I71*.4

Note that this works correctly only if all the grades are available in numeric form in array and no grades are missing. A missing grade should count as 0, however, SMALL ignores missing values in array. For instance, if array contains all 100’s and one missing value, SMALL(array) is 100, not 0. You should use the N function as described in the previous section to take care of that.

Ranking students

The functions RANK and PERCENTRANK can be used to calculate each student’s rank or percentile rank in the entire class. You can use this for information purposes (e.g., when the student is borderline, you might want to know just how low in the pile he or she is, some people like to mention percentile ranks in letters of recommendation, since they are often more descriptive than the bare letter grades: “Was in the top 20% in a class of 140…”). Suppose the area containing the column of final grades (in numerical form) is J82:J85, and Mary Brown’s score is in cell J86. Then her rank in the class would be given by the formula

=RANK(J83, $J$82:$J$, 85)

and her percentile rank (percent of the class scoring as good or worse than her) would be given by

=PERCENTRANK($J$82:$J$85, J83)

If you want to grade on a curve, the percentile rank can be used as the raw score, and the lookup table can be used the same way we converted grade points to letter grades. This time the cutoff would be percentile ranks: 80th percentile or above: B, 85th percentile B+, etc.


©1999, 2001 Richard Zach
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License
.

Leave a Reply

Your email address will not be published. Required fields are marked *