Notes on Spreadsheet Features to Support Effective Grading (Or, What Canvas Can't Handle)

Here are some quick notes about my grading requirements. Each item is evaluated using triage grading, which implies in part that it is graded out of a convenient number of points. All the items in a week contribute to a weekly score. For simplicity, let's say each item in the week is evenly weighted. Note that this does not imply that each is graded out of the same number of universal points. One item might be graded out of four, and another out of eight, but they contribute equally to the week's score. If someone gets 4/4 on the first assignment and 4/8 on the other, then they get 75% for the week. 

If that seems confusing to you, maybe you are a lead engineer at Canvas. It is clear from their interface and the tenor of their discussion boards that they cannot conceive of a model of a course where you don't have some predetermined number of points, and then everything you do doles out those points. I will quickly put it to you, dear reader, that this is a bad model. One of the main reasons is that you have to deal with too many points. Let's say you make your course worth 1500 points, maybe reasoning that there's 100 points available each week of a fifteen-week semester. If a week has two activities in it, maybe one is worth 25 and another is worth 75. Now, you have at least two problems. The first is that you have to determine the exact semantic value of 3/75 of one assignment and make it equivalent in weight to 1/25 of the other, if you're doing an honest job. The other is that you have to make it clear to students the difference between, say, 55 and 56 out of 75 points. By contrast, consider the elegance of triage grading. If I give an assignment that is worth 3 points, and someone does a middling job, they get 2 out of 3 points. Their classmates who also did middling jobs get 2 out of 3 points, but of course, each one gets specific feedback about what aspects were not up to snuff. No ambiguity, no arguing for points, no false equivalence between units. 

My point in this post, however, is not to convince you that you should adopt a coherent grading philosophy, but rather to note for myself how I was able to compute midsemester grades today. This is important given that Canvas—my university's required "learning management system" (ack)—is basically useless for anything but the naive, conventional, point-based course structure.

My game programming course was easy to process because the students turn in one thing each week, but in my game design and human-computer interaction courses, students are doing a lot of different things each week. I decided to use Canvas modules to present each week's required submissions. In Canvas' embedded gradebook, you can view assignments by module, but once you download the data for offline processing, that information is lost. It appears that the columns may be sorted in the order they were created, but the important thing to note is that the connection between column and week is lost.

The first step, then, was actually for me to rename all the assignments on Canvas. I prepended each with an annotation of its week as a two-digit code, so the first week's work was "01", the second was "02", and so on. Then, I downloaded the CSV and was able to import it into Libreoffice Calc. The next step was to wipe out all the Canvas cruft, which was maybe two dozen column headings that I don't care about.

After a lot of tinkering this morning, I determined that life would be easier if I added an extra row to represent the week number. This has a simple formula:

=NUMBERVALUE(MID(AssignmentTitle,1,2))

Let's call that value AssignmentWeek. It allows me to use SUMPRODUCT to do the heavy lifting. My first step was to make sure I could compute the right scores for the first week, so I ended up with a formula like this:

=SUMPRODUCT(NUMBERVALUE(MID(AssignmentTitles, 1, 3))=1, AssignmentScores/TotalPoints)

Once or twice a year I have to remember how arrays work in spreadsheets, so let me say a little more about that function above. SUMPRODUCT automatically works over arrays, unlike other fuctions that require either ticking a checkbox, using curly braces, or pressing Ctrl-Alt-Shift-Meta-Something while pressing Enter. The first argument to SUMPRODUCT is going to be zero or one depending on whether the numeric value of the first two characters is equal to 1. In my case, does the assignment start with the prefix "01" or not. This essentially filters out everything except those columns which are actually Week 1 scores. Then, with the magic of array processing, we can add together the result of dividing the points earned range by the total points range, and this evens out the scores regardless of out of how many points they were graded. 

Now, to generalize this, I set up a series of columns, one for each week, with the week number given at the top. Let's call this WeekNumber. The above formula then can be revised so that we can see the score for each week as follows:

=SUMPRODUCT(NUMBERVALUE(MID(AssignmentTitles,1,3))=NUMBERVALUE(WeekNumber), AssignmentScores/TotalPoints)

The result of that expression above needs to be divided by the number of work items given that week. This cost me a lot of time trying to sort out, because it doesn't work the same way as the WeekNumber reference above. Instead, the expression looks like this:

COUNTIF(AssignmentWeek,"="&WeekNumber)

This gives a final form like this, which is copied into each cell of a matrix that shows the week's score for each student:

=SUMPRODUCT(NUMBERVALUE(MID(AssignmentTitles,1,3))=NUMBERVALUE(WeekNumber), AssignmentScores/TotalPoints) / COUNTIF(AssignmentWeek,"="&WeekNumber)

Looking at each student's score for each week allows me to drop in a simple formula to drop the lowest and determine their midsemester grade.

It took a lot of time this morning to sort this out, so hopefully by recording my notes here I can save myself time later, and maybe even help folks who come across this via search results. If you happen to know an easier way to meet my requirements through a spreadsheet, let me know. It's clear to me that Canvas is not robust enough to handle something like this. That's a shame, since my students are so addicted to the illusion of data that comes from seeing "current grades" or "midsemester grades". I'd rather blow up the whole thing, but I'm not ready to move to Evergreen just yet.