UW Ellbogen Center for Teaching and Learning, Instructional Computing Services

Spreadsheets for Grading

Last update: 27 October, 2011; R. Hill

Spreadsheet software provides rows and columns of cells, to be defined in terms of values, labels, and forumulas involving other cells.

For a standard grade book, use rows for students and columns for grades, titles on top and left, values in the lower right portion. Freeze the titles ("Window" menu).

Example: Consider, for the fictional course CS 1090, of about 30 students, the cumulative contribution of two homework grades, where Homework #1 has 15 possible points and counts for 2% of the total grade, and Homework #2 has 40 possible points and counts for 5%.

Setup, where formulas are entered into column M: Display:
J K L M
... Hwk1 Hwk2 HwkContrib
Student in row 19... 11 29 =(K19/15)*0.02+(L19/40)*0.05
Student in row 20... 14 31 =(K20/15)*0.02+(L20/40)*0.05
J K L M
... Hwk1 Hwk2 HwkContrib
Student in row 19... 11 29 0.051
Student in row 20... 14 31 0.057

Even better, use a row to record the possible points and weight of each assignment.� Absolute references to cells (with dollar signs) will not change when the formula is copied, in this case, from one row (one student) to another.

Setup:

J K L M
... Hwk1 Hwk2 HwkContrib
Student in row 19... 11 29 =(K19/$K$37)*$K$38+(L19/$L$37)*$L$38
Student in row 20... 14 31 =(K20/$K$37)*$k$38+(L20/$L$37)*$L$38
... � � �
Possible (row 36) 15 40 �
Weight (row 37) 0.02 0.05 �

The resulting Display will be the same, but the points and weights can be easily adjusted if necessary.

Examine these versions for various other options.

CS1090v0.xls Data entry with some values of interest on bottom rows
CS1090v1.xls Cumulative homeworks, letter-to-numeric IF formula
CS1090v2.xls Array formulas for cumulative percentage and points
CS1090v3.xls Several sheets, for different purposes, cross-referenced
CBgrds.xls Several sheets, formatted and colored for efficient use

Final Hint: Save a worksheet in '.csv' format ("comma-separated values") for archival, reading as text, and use by other applications.