Well, I switched from an old Fortran grade program to Excel when they put our old VAX out to pasture at the turn of the millennium (the many zero's one), thinking I would not be able to do things as easily, but in fact I was wrong. However, it was not easy to figure out how to do some of the more sophistocated things one wants a grade sheet to do. First I relied on our local IT help, which did provide a solution for what I wanted to do after a struggle, but then later I wanted to push it a bit further (don't we always want more...?). The IT person was not around the day my interest peaked. A web search on "How to use Excel for Grades" turned up one link which was informative but the linked worksheet did not open and the other few links it had to Excel grade sheet information were less useful. Strange that there is not more on the web considering the obsession with grades in this country. (In fact an IT workshop on using Excel for grades was canceled due to lack of interest this semester: most people are content to continue doing grades the way they always did them, perhaps by hand...?!) Then I hit a local book superstore and browsed fat Excel books for a half an hour, and got a partial clue, but it was a stupid formatting thing (no decimal places showing) that had convinced me that what I was trying already was not working, and by chance I figured it out on my own by stumbling around enough with attempts. But you don't care about this.
My example spreadsheet has input columns for some quizzes, some tests, and some outside assignments (MAPLE). It is very unsophistocated, and meant only for those even less sophistocated than myself who want to get started. You must at least have some elementary knowledge of Excel to use it, since I am not going to write detailed explanations here. It allows manual discounting of lowest grades (although this can easily be automated), computes student averages, converts averages to letter grades by looking up in an equally spaced cutoff grade table, converts the letter grades back to the famous 4.0 scale and averages all the columns. Since my quizzes are on a 1-10 scale, tests 0-100, and MAPLE 0-2 per assignment so I only keep a running total, I convert these all to 0-100 before averaging together with weights to evaluate student cum's. I have a NoGrade Grade option for incompletes at the end of the semester. You can easily modify the formulas I used here to create your own from scratch.
The key command for grade conversion is HLOOKUP. You need the FALSE option to convert exactly from the list of letter grades back to numbers, and you must not have this option for the conversion from the initial raw number scores to letter grades where the grade cutoffs must therefore be ordered in ascending order for the command to select the highest letter grade cutoff which is equal to or greater than the input number score. I prefix lowest grades by the letter "x" to convert them from number data to text data so that they are ignored by averaging functions (COUNT only counts nonblank cells with number data, ignoring cells containing text, as does the SUM function used in the averaging). Similarly I use "E" for an excused no grade, again leading to its being ignored in computing averages. One can use the MINIMUM function subtract away a lowest grade for a set of columns, but I started out wanting manual control and am too lazy at this point to edit the formulas. [So my class averages for individual quizzes and tests do not reflect the original grades before deletions.]
At the top of the spreadsheet you will see the Maximum Grade for my 3 grade categories to indicate their ranges and their Weights in the weighted average for the cum. I should add another pair of entries in the column preceding these over the final exam column to weight the final exam differently, but I just treated it as a 4th exam this semester. This would just add a separate entry into the weighted average for the cum. The averaging functions for each student grade category and each grade column are just SUM(<cell range>)/COUNT(<cell range>) and so will give errors if no data is present to be averaged (division by zero!). A more sophistocated spreadsheet would test for a zero count perhaps. The raw cum is converted to a letter grade from the grade cutoff table, and then the letter grade is converted back to the famous 4.0 scale and averaged to see what university statistics will report as the class grade average. An extra column allows manual Up/Down Grading a student to override the automatic letter grade assignment, and an IF construction tests if that entry is zero to use the automatic letter grade assignment in the final column of 4.0 scaled grades. The equally spaced grade cutoffs are set by selecting the lowest (D-) and highest (A) cutoffs and equally spacing the cutoffs in between automatically. This is where the grade fudging takes place.
You must format your number data cells to show 1 or 2 decimal places as desired. My first experiment with letter grades back to 4.0 grades seemed to fail with the plus/minus grades until I realized that by default the cell was only showing 0 decimal places instead of the 2 decimal places I was expecting. Also you want to set up the default printing to be landscape through the Page Setup on the File Menu. These grade sheets are invariably wider than they are deep unless you have way too many students in your class.
For printing purposes, to get the gradesheet onto a single US sized sheet in landscape mode, a horizontal grade table was positioned under the grade data. This means adding or deleting columns for alternative numbers of quizzes or tests will mess up the grade table which has to be re-edited back into place. Placing it as a vertical table after the class data (and using VLOOKUP) would lead to the same problem if a student row is deleted and make it too wide to print on a single sheet.
Cell T4 (Auto Letter Grade Cum):
=HLOOKUP(S4,$D$32:$Q$33,2)
This takes the input raw number score S4 and looks up in the first row of the first two rows of the grade table (cells $D$32:$Q$33) and returns the second row entry (last argument: 2). Absolute cell references are obviously needed here and below for copying down the column.
Cell V4 (4.0 Grade Scale Cum):
=HLOOKUP(IF(COUNTBLANK(U4)=1,T4,U4),$D$33:$Q$34,2,FALSE)
This first checks if the Up/D-Grade column (U4) is empty or not. If empty it looks up the letter grade cum (T4) in the last 2 rows of the grade table ($D$33:$Q$34) and returns the corresponding second (2) row 4.0 grade. If not empty, it passes the manual override letter grade (U4) to the 4.0 scale conversion. The FALSE option is necessary for it to make an exact match, which saves one from reordering the letter grades by default computer ordering (B B- B+ C C- C+) etc instead of corresponding to numerically ascending 4.0 grades if no FALSE option is present. N for NoGrade (incompletes) passes directly to N.
Cell E35 (Grade Bin Width):
=(O32-E32)/10
This takes the D- and A cutoff spread and divides by the 10 grade bin widths which separate them with plus/minus grades and no A+. This is then added to each previous cell starting with D to get the intermediate cutoffs.
When I checked my own download of the Excel spreadsheet, I got the same error as at the Berkeley site above: "Do you want to open this as read only...?" and either way I answered got an "Unable to Open File" error. IT advised me to remove the checkmark on the "archive" property of the worksheet before posting it. It worked.
9-may-2000: robert.jantzen@villanova.edu