I’m going to try SBG next year, I *think* in all my classes (Precalculus, Chemistry, and Physics), and I want to start thinking critically about it now so that I have as few regrets as possible for the following year.
The Setup
First, I’m blessed in that my principal doesn’t mind whatever grading system and/or computer program I use to record grades, as long as it is easy for parents and students to see where they are. I hope that mine makes it even easier than before.
I didn’t want to fork over the money to pay for Active Grade (even though it looks awesome), and the school I teach for is currently poorer than I am, so they sure aren’t going to pay for it. So I turned to Google Spreadsheets.
Google Spreadsheets has a function called “importRange”, which pulls cells from one spreadsheet into a separate spreadsheet. The function can be finicky (which is one of my only major hesitations), but besides that, it’s great because it allows me to share one spreadsheet with each student (a good job for a TA at the beginning of the year) and then students can see their own progress on each assessment.
“But that would take forever to setup and get just how you want it!”
Too late, I already spent the time making it. Here it is.
So below I’m going to show you an overview of what it is. If you want to play around with it, you’ll have to make a copy of it, fix a small handful of keys (really just the “teacher key” in the “gears” sheet of all the student spreadsheets), and then you can tinker to your heart’s desire. go for it cause I don’t need it anymore (just try to keep it similar for those who also might be interested in it after you).
But First…
Before I get too into it, I really have to thank Jim Pai, with whom I started having some great conversations about SBG and who helped me think about what I really wanted. I’m still wrestling with many of these issues, and hope to have many more great conversations with Jim and others, so I’m as ready as possible to start SBG next year.
What the Student Sees
All of my students will be required to have Google accounts next year (not sure how much I’ll do with it, but it looks very promising–just go over to Julie’s blog and you’ll see!)
I will share a separate spreadsheet with each student. When they open it up, they’ll see something like this (after they’ve had some assessments)
Notice that I’m starting simple: 1, 2, 3, and 4. (I was going to stay away from numbers with B, D, P, and M, but numbers are easier to do things with in spreadsheets, as I found out). The numbers are also nicely color-coded along with an explanation on the right. Each standard could have a comment, along with a generic comment just under the student’s name (“Alice” is an example–right now I have not students with that name). All of these fields, including the name, are pulled from different areas of the teacher spreadsheet, so once the class is setup, I’ll never have to open up a student’s spreadsheet again! [1]
You’ll also notice the “Details” and “Gears” sheets at the bottom. The “Gears” are the formulas that go on in the background… which is why I make these sheets un-editable by students because they could really screw things up in there. If the student were to click on “Details”, they’d see something like so:
Notice that I now have every assessment listed here, along with every reassessment and even certain “assignments” like “Talked to student”. I’m starting to feel the SBG buzz. This gradebook is infinitely better than my previous one, where if a student reassessed, I’d have to replace the old grade with the new one. Now I can just enter it right afterwards and we everyone (the student, parents, and myself) can see how the student progressed. How do I calculate the “summary” grade? I’ll talk about that down below.
Also notice how the spreadsheet opens with the “Summary” rather than the “Details” because I want students to focus more on the standards than the specific assignments. Hoping to foster that discussion of “what do I need to understand” rather than “where can I get these points?”
What the Teacher Sees
Now, one of my goals was to make it as easy as possible for me, as the teacher, to enter grades, comments, new assignments, AND new standards, and sure enough, I believe that Google Spreadsheets delivers, thanks to the “importRange” function.
Here’s what the teacher sees when he/she opens their (separate from the students) spreadsheet.
Disclaimer: all the student names are examples, (no my class does not incredibly have one student with every single letter of the alphabet in it, though that would be awesome!).
So teachers enter “grades”, one for each assessment (running across the top) for all the students (running down the left side). The standards are placed above the assessments, grouped so that all the subsequent assessments for a standard are to the right of the first assessment, and these can even be further grouped into units.
To add a unit, standard, or assessment, the teacher simply inserts a new column (there are at least 3 ways to do this in Google Spreadsheets) and fills out the appropriate names at the top.
Student didn’t take an assessment? Just leave it blank for that student–doesn’t affect the calculation.
Need to add an assessment for a previous standard? Just insert it in after the last assessment for that standard. Right now I think I have it setup to take in up to 120 assessments. That should be enough for a semester, right?
So you want to check on class trends rather than each details assessment? Click on the “Summary” tab and this is what you’ll see.
First, notice that this is an example, so I’ve only got grades entered for a few students. But here, you can see that you can enter comments right next to each standard, and students can see these on their spreadsheet! There’s also the “Generic Comment” that goes right next to the student’s name on the left side. I suppose that if there start to be too many comments, you could freeze the first 2 columns so that is always there.
Want to see it with the students’ names along the top, but standards on the left? There’s a tab for that: “Summary Transposed”. Would rather see the details like that? “Details Transposed”. I’ve also got another tab “StudentList” where you can enter student’s names, e-mails, and other important info (which I’m going to get at the beginning of the year next year with a Google Form, so it’s easy to copy). All the other spreadsheets pull the names from the “StudentList” sheet so it’s all centralized. There’s even a tab listing the “Standards” and the “Assignments” (along with about 7 “Gears” tabs for running all the necessary calculations in the background.) [2]
Now, about those calculations…
About the Calculations
I waffled back and forth between a few ideas for figuring a student’s standard, and although I could have a whole post on this debate, that’s not the intent of this post, so I’ll try to keep it brief.
I started thinking “students should only be allowed to improve their standard, because otherwise they won’t reassess”. But then I read this post.
So I started thinking “okay, I should take the minimum of their last n assessments”, and I was going to go all jujitsu on the spreadsheets and make n changeable with a single cell, but then I realized that was way harder than I had thought.
Then I thought some more about how students would have to take several more assessments to make up for one “bad” assessment which doesn’t seem right, even if I’m using SBG to figure out what a student knows rather than how well they do on a given day.
So I settled on this plan: I’m going to take the average of the last two assessments taken by the student. These could be teacher-initiated or student-initiated.
Furthermore, I’m probably not going to tell students that this is how the formula works [3], lest they become too focused on “just doing enough” or “just getting the right score” and less focused on the standards themselves.
The nice thing is that this formula works even with blanks: three scores of {3, null, 4} actually average out to 3.5, whereas {3,1,4} [4] would average out to a 2.5. [5]
Have 10 assessments of 1’s and two 4’s? Well, then, in my book (quite literally, right now), the student has mastered the content (see [5] again).
Some Kinks & Cons
As I mentioned above, “importRange” is a bit buggy at times. Initially I actually had the teacher’s “Summary” page pulled from each student’s spreadsheet because the calculations were way easier. Then I noticed that nearly 100% of the time, at least one of the 3 mock students I had setup at the time didn’t have any grades. Now the teacher’s spreadsheet is totally independent and each of the student spreadsheets really only pull twice: once for the details and once for the summary+comments.
Another issue is that I’m not paying for this service, so if my spreadsheets accidentally get deleted, I’m up that creek (“crick”?) without a paddle. So lotsa backup often will be required, at least until I become more trusting of Google’s Drive service. (Haven’t had any concerns yet, but wouldn’t it be unfortunate if the first time was when it mattered most?)
Another Con is the limited control I have over my formula. Sure, I can change it, but then it changes for everyone. I think a student actually had a concept mastered, but they have two scores: a 1 and then a 4, then the gradebook will require that they take another assessment just to make sure. Which perhaps is a good thing?
How do these translate into grades for the gradebook required by my school? Well, I’m still working on that. Tentatively, right now, I’ve got them pegged at about these ranges: 4=100%, 3=80%, 2=50%, 1=0%. Maybe the 1 is a little harsh? But I want them to show lots of “proficients” as a minimum, so perhaps not.
Oooh, one more thing: the comments are setup so that if you enter a standard “out of order” (add a standard before another standard), then all the comments get kicked over to the wrong standard. Fortunately, if you know this ahead of time, it’s an easy thing to fix, but you may have some very confused students for a while.
Some Pros
This is completely free! Yeah, it took some time to make, but now that it’s made, it takes very little time to upkeep (fingers crossed), and it will hopefully be just what I’m looking for as a first year attempting SBG. Yes, I need to take it for some more test runs before I’m sure all the kinks are worked out, but I’m hopeful.
SO easy to enter grades. I always wondered why grade-book programs didn’t just use a simple spreadsheet to enter grades: if you make a mistake, “undo” is right there! And moving between cells with arrow keys and/or tab is so much easier! And it just feels more comfortable (perhaps because I’ve used spreadsheets way longer than grade-book programs?)
Feedback
Here is where I need your feedback on this. Tell me what’s stupid (I won’t feel insulted) or crazy, or what you like. And if you’d like to know more about it and/or try it for yourself, I’ll gladly share, so please let me know that, too! I actually am getting excited about grading for the first time in my life, and can’t wait to start SBG next year!
[1] Except that I forsee myself opening these up to explain to students & parents exactly what we’re doing here.
[2] I was actually worried about reaching the Google Spreadsheet complexity limit of 40,000 cells with formulas. Oops…
[3] Some will inevitably figure it out.
[4] The resemblance to pi was totally unintentional, but yes, I suppose I did spot it…
[5] Whether you think that is a good result or not is a philosophical education debate, but the spreadsheet math works, darn it!