SPREADSHEET PROJECT

Spreadsheet Project

Spreadsheet Project Guidelines

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 is provided, which will be referred to below. Right-click on the appropriate link and choose "Save Link as...": [Excel Spreadsheet ]

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 ...