Using a gradebook with alternative grading, part 2: Final grades
How can I use a spreadsheet to figure out final grades?
Last week, I introduced my method for using a spreadsheet to record grades in an alternatively graded class. This week, we’ll look at how you can use a spreadsheet to determine final grades. If you haven’t read Part 1 of this series, do that now! We’ll build on those ideas in today’s post.
As with last week, I’ll be using Standards-Based Grading to illustrate the new ideas that arise in using a spreadsheet. The ideas here apply equally well to Specifications grading and other variations. In addition, while I’m illustrating my ideas with a spreadsheet, thees ideas can work with even the most ornery of Learning Management System gradebooks.
What does the gradebook look like?
In my first post, I made three key points about organizing an alternative gradebook spreadsheet:
Assignments cover multiple columns, so you should have one column per standard assessed on each assignment.
Record marks as numbers (e.g. Satisfactory/Revisable/Not yet becomes 3/2/1), because spreadsheets work better with numbers.
Create automatically calculated “summary columns” to see a student’s current progress on each standard.
By the end of the semester, your gradebook might be getting quite wide. There will probably be a lot of columns: columns for standards or specifications on each assignment, summary columns for each standard, and columns for any other notes or data you’ve recorded. This is OK: Spreadsheets are good at handling a lot of data. It’s what they’re best at!
For example, here’s how my summary columns look by the end of the semester. You might need to click to zoom in and see all of the fun.
You can see that I have 29 standards in this class, with “short codes” like P.1, N.5, S.4, etc. (listed in row 2). The numbers underneath each column indicate how many times the student has earned an “S” (Satisfactory) for that standard.
I’ve scrolled over – way over – because columns H through GS contain student marks on various assignments. You can see examples of them in last week’s post. This week, we’ll be focusing entirely on the Summary columns and beyond. Sometimes, to make the spreadsheet easier to handle, I move the Summary columns into their own tab.
Grade tables and grade summary columns
A common way to describe the requirements for final grades in an alternatively graded class is a grade table like this one from my class:
For example, to earn an A, a student must do all of the following: meet the Communication Specifications 10 times, meet 26 standards, and those 26 standards must include 10 of the “Proof Method” standards (these are P.1 through P.10 in the above gradebook screenshot).
Others might write a bullet-point list of requirements, or a narrative paragraph. As long as you are able to put clear, numerical benchmarks on each grade, then what I’ll describe below will be useful to you.1
Back to my grade table: To determine a student’s final grade, I need to be able to see how they’ve done in each of the grade’s three categories. To do this, it’s time to create some more summary columns! This time, they will be grade summaries. Here’s what the final result looks like:
These columns are located to the right of the “Standard Summary” columns shown above.2
An example of how to read this: Alice has earned an A, because she’s met (or exceeded) all of the requirements in the “A” row of the grade table. Bob, unfortunately, has only fully met the requirements of the D row, mainly due to communication specifications. Even though he’s above “D” level in other categories, he hasn’t fully met the requirements for a C or B. Likewise, Chris has earned a B.
Let’s step through the process of making each of these three grade summary columns.
Communication Specifications: As I mentioned last time, I use communication specifications on some assignments. I record whether students have met these specifications by adding a column labeled “C” under each specs-graded assignment, and I enter either a 3 (Satisfactory) or 0 (Not Yet). You can see an example in column H in this screenshot, where Alice has met the specs, Bob has not, and Chris hasn’t been assessed on them yet (so their “C” column is blank):
To create the Communication Specifications grade column, I need to count up how many times there’s a “3” in a column labeled “C”. Luckily, our old friend, the COUNTIFS spreadsheet command comes to the rescue. Here’s the command that I entered it cell HX3:
=COUNTIFS($G$2:$GN$2,"C",$G3:$GN3,">=3")
Since we talked about COUNTIFS last time, I’m not going to go into as much detail — but you can read more in last week’s post. In brief: The first two parts ($G$2:$GN$2,"C") focus us only on those columns in row 2 – the row where I list what I assessed on each assignment – that have a “C” for “Communication Specifications”. The second part ($G3:$GN3,">=3") then counts only the corresponding cells in Alice’s row – row 3 – that have a 3 or higher in them. Together, these count the “C”-labeled columns in which Alice has a “3”. I then copy this command to the rest of the cells in the grade summary column HX.
Total Standards Met: To “meet” a standard, my students need to earn at least two “S” marks on it (this helps demonstrate that they’ve retained those skills over time). If you take a look at the gradebook near the top of this post, you can see that the “Standard Summary” columns record exactly what I need: How many times each student has earned an S on each of the 29 standards.
That means that I need to count up how many of the Standard Summary columns have a 2 or greater in them. It’s time for COUNTIFS again, although a somewhat simpler variation. Here’s the formula for cell HY3, which records how many standards Alice has met:3
=COUNTIFS(GT3:HV3,">=2")
The range GT3:HV3 represents the Standard Summary cells (columns GT through HV) in Alice’s row (row 3). You can see these cells in the first image in this post. The second parameter, ">=2", counts how many of those cells have a 2 or greater in them. Because students can sometimes earn extra S’s on a standard, it’s important to use “>=” here. As before, I copy this command to the rest of the cells in the summary column HY.
Proof Methods Standards Met: This is almost exactly the same as Total Standards Met, but this time I’m only counting the standards named P.1 through P.10. These are especially important standards in my class, so I add an extra requirement to my grade table that students meet a certain number of them.
Those Proof Methods standards are summarized in columns GT through HC, so the command looks an awful lot like the one we just saw:
=COUNTIFS(GT3:HC3,">=2")
This command comes from cell HZ3 and is copied down the whole column.
How to use the summary columns
So, how can I actually use these grade summary columns to determine a final grade? There are a few possibilities. At the simplest, I just read down the summary columns and compare the results to the grade table. Then I type the corresponding letter grade in a new column off to the right.
As you’ve undoubtedly noticed, I love color coding. So another option is to set up conditional formatting – a common spreadsheet feature, look under the “Format” menu – and then use the colors to guide you to determine the student’s final grade. For example, when I see that Alice has a solid green row of grade summaries, I can quickly tell that she has earned an A. I have to think slightly harder about Bob and Chris, but this becomes easier with a little practice.
These approaches work with other forms of alternative grading as well. We’ve already seen above how specifications can work into a grade table. Even if you have some grade table entries that are “yes” or “no” (such as requiring a student to complete a project in order to earn an A), you can still think of these as numerical data: 1 for yes, 0 for no, and enter this information directly into its own grade summary column. I also sometimes use completion of ungraded work, like pre-class preparation, to set the “plus” or “minus” grade for students.
These final grade summaries also help you use alternative grading with a Learning Management System’s gradebook. You can upload the grade summary columns periodically to a LMS gradebook, entering them as custom columns, to give students an idea of how they’ve progressed in each grade category.
Finally, It’s also possible to get the spreadsheet to do even more of the work, by writing spreadsheet commands that actually determine the grade for you. The best way to approach this is the VLOOKUP command. In brief, VLOOKUP looks up a value in a “lookup table” and then returns another value from that table. In this case, you could create a lookup table that’s basically a spreadsheet version of the grade table, and write a VLOOKUP command that finds (for example) the number of standards completed in the table, and returns the appropriate letter grade. To describe this in detail is another whole blog post that would get ever deeper into the spreadsheet weeds. If you’d like to see it, let me know in the comments!
A word of caution
There’s a potential danger with using a spreadsheet to record alternative grades: Spreadsheets can only do so much, and we all have limited time, spreadsheet knowledge, and patience. You might have a great idea for how you want your alternative system to work, but can’t figure out how to get a spreadsheet to work with that plan. Don’t let the spreadsheet dictate how you grade.
For example, you might want to look at whether a student’s most recent marks on a standard are “Satisfactory” or not. My approach to spreadsheets doesn’t handle “recency” very well. Rather than giving up and changing your grading approach, consider changing the organization of the spreadsheet instead. For example, you could record a student’s marks on each standard together in one group of columns, with the rightmost entry being the newest one. You might even ditch the spreadsheet and go back to good old pen and paper.
If you’re focused more on ungrading, be sure to check out my advice at the end of last week’s post. Spreadsheets can still help you keep track of your feedback and notes. But avoid trying to fit a square grading peg into a round spreadsheet hole: Find something that helps you do what you want to do, not vice versa.
Whatever you end up doing, I hope that you’ve found these two blog posts helpful. I hope that with the advice and spreadsheet commands above, you can put together a useful gradebook that makes your alternative grade recording easier and simpler.
If you’re using ungrading, student interviews, portfolios, etc., you can still record that information in a spreadsheet – see last week’s post for some details – but the method I describe here is not best suited for that kind of situation.
You might have guessed by now that I really like color-coding things in spreadsheets. Yes, I used the same color coding as I used in the grade table – humans are hard-wired to work with colors, so this really helps me make sense of the data!
You could use the simpler command COUNTIF here, which is exactly like COUNTIFS but only uses one set of criteria.