Expanding the SBG Tracking Gradebook

I mentioned Evan Weingberg’s standards-based tracking system in a short snippet a couple weeks back. I want to expand on that thought a little bit.

Essentially, he uses a Google Sheet to aggregate quiz performance to make sure standards are actually learned over time. This has been incredibly valuable to me because I felt that students were growing, but had no solid longitudinal data to back that up. It’s not 100% objective (nor should it be), but it helps inform and confirm (or refute) my gut feeling about a student.

I grade my quiz questions on a 1-4 scale rather than Evan’s 1-3 using a rubric I made years ago with significant help from Jenn Binis.

2016-03-24_11-40-48

This is posted in my room and helps me give more specific feedback to my students. The quality in responses and in corrections has jumped dramatically. The rubric is also nice because it can be adapted to just about any question.


I give numeric feedback to students on their quizzes, but most of the time, students toss them in recycling on the way out. I’d like to have a way for students to take a look at their longitudinal growth over the course of a chapter. The idea is to take Evan’s original format and create a companion sheet which is specific to each student – some kind of template mixed with doctopus so I can update a master sheet and each student also can see their personal growth on a connected reporting sheet.

I’m not 1:1 this year, so implementing it before the end of the semester isn’t a major priority, but it’s something I’m going to tinker with over the summer to see if I can’t work out.

When Class Itself is a Distraction

I’m going to stop doing labs in one of my classes this semester. Regardless of the supports or steps I put in place, there is a general refusal (save for 4 students who are trying their hardest to rise above the din) to follow procedure, listen to instructions, and work safely.

The class itself has become its own distraction. There lacks a visceral awareness of how actions impact others in the room, which has led to a general disregard for any form of structure. Labs are highly desirable because of the experience students get with the science, but they’re not teaching anything. It’s effort I put in that isn’t spent on a significant learning gain. It’s also creating liabilities.

It’s all the more frustrating because I think they could do well with labs, but the class is fighting me for control. Power struggles aren’t suited for the lab space. For now, we’ll put those on the back burner until such a time we’ve regained enough self-control to work safely and effectively.

Growth is a process and this year, it seems to be an extra intense one.

Easily Filter Large Data Sets in Google Sheets

I use Google Sheets in my classroom a lot. I used to rely on combinations of the vlookup, importrange, and index/match functions to get information, but I’ve recently switched to using if and filter to return arrays of information from master spreadsheets.

Using filter is nice because it takes multiple conditions and you can set which columns of the array you want to return for your summary sheet. A pretty standard search looks like this:

=if(filter($A$2:$A,$A$2:$A=$F$3)=$F$3,filter($B$2:$B,A2:A=$F$3),"")

Here’s a sample spreadsheet so you can see how the result is returned after changing the filter term.

So, let’s break it down:

Cell F3 holds my search term, “A”, “B”, or “C”.

(filter($A$2:$A,$A$2:$A=$F$3) – Filter looks through a range of cells ($A$2:$A) for a specific condition ($A$2:$A=F3), much like the IF statement. The exception is that this only returns the matching content rather than a boolean (true/false). The filter, in this case, is serving as the boolean check for the IF statement it’s wrapped within.

=If() – This function is super helpful because it limits what happens in the sheet. It’s like conditional formatting, but for your functions. It takes two arguments, minimum, but you can set up to three: the condition to check, what to do if true, and what to do if false. In this case, the conditional is set with the filter function (see above). If the filter returns a cell with an “A” in it, the TRUE condition is run.

filter($B$2:$B,A2:A=$F$3) – If it’s true, I want a different column returned. In this case, it’s the names of students with group “A” set. Filter works the same way, except this time, it searches for column B (the names) that match the search parameter (“A” in column A).

In other words, the function reads like this:

  1. If

– filter through column A

– Look for cells that contain “A”

– If an “A” is found, the IF statement is TRUE

  1. Execute the “TRUE” parameter

– Print the student’s name in the cell

  1. If not, leave a blank cell

I added a third column, which prints the student’s project content just to show how these functions can be used in conjunction with one another.

I know you can use ARRAYFORMULA to do essentially the same task, but using ARRAYFORMULA doesn’t allow you to add custom content in the column – the throws an error saying data cannot be overwritten. I don’t run into that case often, but it’s often enough to be annoying.

Again, this is difficult to see without checking out the example spreadsheet. It’ll take some playing, but once you get it, it’s very helpful. Leave a note in the comments if you get stuck and need some help working it out.

The Power Game

I did something I normally don’t do: I had my students play a computer game for class the other day.

We’ve just finished nonrenewable resources – specifically coal and oil. We rounded last week out with a portion of the documentary, Switch, which gives a great nonpartisan look to oil and coal use in the developed and developing worlds. Students understood – in theory – that we are facing not only environmental challenges, but economic as well.

I’d been trying to come up with some kind of simulation for students so they could really experience that economic and environmental conflict. I didn’t really find a good one, so I booked one of the computer labs and we played Energy City. It’s a game created by The National Geographic Society and The JASON project. Think simpler SimCity with less people-managing and more resource managing.

I liked this as an inquiry activity because most students dive right into producing energy with coal and oil because it’s cheap and powerful. They tend to focus more on saving and making money rather than conserving environmental resources. It was often too late to come back from failure by the time they realized that air quality and overall environmental health don’t recoup nearly as fast. A lot of games ended in losses pretty quickly.

It really helped students understand they need a balance. They couldn’t dive in without projecting long-term costs. Most started looking at the per-turn cost rather than the up front. They also looked at what actually improved their environmental impact rather than degraded.

Teaching Thoughts

Some things I’m thinking through this week, written here so I can find them later.

Evan Weinberg has a good string of posts on his blog related to student assessment. This one in particular has me thinking through 1) what I assess, 2) how I’m assessing, and 3) what information I actually get from students on the assessment.

– I really struggle with setting consistent classroom expectations, which has led to a lot of the frustrations I’m dealing with in class.

– My instruction, even though I often call it “student centered,” is still too focused on me.

– But I’m not sure, because of the two items above in conjunction, that I’ve moved my students to a place where they can be self-directed more often.

– The influence of standardized testing on our students is increasingly negative and I need to keep a positive attitude during the testing windows even though I’m incredibly frustrated.

– I need to make consistent time to either code or read, because those have helped me relax more than anything else these days.