### boring history (ignorable)

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

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.

### the tricky formulas

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.