A Google Sheets based standards gamification system

As described in a previous post, I recently decided to try a mastery-based grading system with gamification for my geometry classes, inspired by some amazing work from Kyle Pearce and John Orr. Implementation of the system is key, and they had together, with the help of Alice Keeler, created one of the most amazing pieces of Google Sheet work I have ever seen to help with this; you can see his post explaining it here.

Here is the highlight version:

The spreadsheet has one tab, called “Master,” which controls most of the system. On this tab you define your standards or learning goals for the unit/course, put your roster, and assess each standard. Students can earn from 0-4 stars on each standard (though you enter it as a standard 1-100 grade). You can also award Mastery Badges to students when they have, in your estimation, mastered a goal. You can also add feedback, links to assessments or resources, and notes either to the class, a student, or to a student in response to a particular standard.

All of this is automatically imported by the students’ own personal tab. They can see their grade on every standard, which ones they have mastered, how many they’ve mastered, and what level this makes them. Their goal is to level up to the highest possible polygon! This can then be published to the web so they can see it as their own personal Mastery Portal, with links, feedback, and so forth that (should) automatically update. It looks like this:


All of this was awesome, but I immediately saw some things I could do to improve the sheet; my summer of working as a spreadsheet programming professional really came in handy here!

I made three major changes to Kyle’s sheet:

  1. I sped it up. The original student sheets relied extensively on HLookup and VLookup calls, which are amazing spreadsheet functions that also tend to be rather slow when used a lot. I was able to use some different commands (Index and Match) to speed up the calculation of student spreadsheets by limiting the number of times one sheet looks up data in another.
  2. I added some automation. Specifically, I added scripts to automatically create the student tabs from the roster, automatically get their URLs so you don’t have to copy and paste links one-by-one from a menu, and delete all of the student tabs if you need to start over. I also added a script to force the student tabs to update if for some reason they don’t change when you enter a score. Thanks to Alice Keeler for her TemplateTab script from which I started and got inspiration.
  3. I added a little bit of customization that was not in the original (though not as much as I’d like to add eventually)
  4. I added a tab with directions, so there’s no need to reference a blog post to remember how to work it. =)

I’m very excited to use this spreadsheet for this unit. Thanks so much to John Orr and Kyle Pearce and all of their inspirations for the brilliant idea and work – I think this could be a real game changer.

Click Here to get your own copy of the Gamified Standards sheet

I’m Gamifying Learning Goals with help from the MTBoS

On Thursday night I was up until about 11 pm – far past my normal weeknight bedtime – working on finishing some grading as midquarter grades were being posted the next day. As I worked my way through 40+ copies of a Big Unit Test, I realized that I was being surprised by them more often than I’d like.

I’ve never mastered formative assessment. I have a hard time putting emphasis on it and time into it for the same reason that my students do – I’m a procrastinator and heart and work better with deadline and attached value. So we worked through a right triangle trig and area test in my geometry class, and some students never really got it, and I didn’t know that. Then there were some students who just screwed up on test day – as one student told me later, her test grade was collateral damage to a lab report. And there were just as many positive surprises – which is nice, but still tells me I didn’t know what I was doing.

I wrote this tweet:

And then, after finishing grading and writing necessary comments, I stayed up a little later, in a tired-but-annoyed fugue state. I stumbled upon this tweet by Kyle Pearce:

Go ahead and read his post. I’ll wait.

I followed the link, read the post, and realized that I needed to try it out. Immediately. And I couldn’t wait. I decided that I would try it starting the very next day, with the unit I had already been doing for two days with my geometry classes: circles.

The next morning, I had 80 minutes to prepare for my first geometry class. I was able to get their names entered on Kyle’s spreadsheet, create a sample web page to show them, get some preliminary standards written up, and make assessments for my first three standards – naming parts of a circle, sketching parts of a circle, and moving between area, circumference, and diameter of a circle. You can see the assessments I made here: Circle Standard Assessments .  The assessments are not particularly clever or good – I made them fast – but it’s a start. I ended up doing standards 1 and 2 at the end of class with them, and assigned standard 3 as homework – they can either do it for practice and attempt it again later OR pledge not to use notes/books/others and do it for Mastery (we have an honor code that makes it reasonable for me to offer this option).

I’m really excited about this. I think it is going to be awesome. My students were excited as well.

If you want details on implementation, see this post on how exactly to use the spreadsheet to implement this system, with some modifications I added.

Multi-part area problem

I merged the study of triangle trigonometry and polygon area in my geometry class, since they go together very well. For their test, I created this multi-part area problem I like quite a bit. You can click the image to access the Geogebra sketch I used to make it on GeogebraTube if you’d like to download and modify it.

area problem
Click the image to see the Geogebra sketch I used to make it on GeogebraTube.

3 Act Trial (and tribulations)

Inspired by the general enthusiasm for the process in the MtBOS, I tried my first 3 Act Math Lesson(s) today.

If this is somehow the first place you’ve heard that term, Dan Meyer has been the main force and originator of this structure, and I’ve also seen good explanations of the structure from Dane Ehler, Geoff Kraal, and many other. I think this series of blog posts by Dan Meyer is maybe the best place to really start understanding the structure.
We just finished basic triangle trigonometry and word problems, and are about to start applying trigonometry to area, so I was looking for problems to help us hone in on those, and I found a couple: Boat on the River by Andrew Stadel and the Big Nickel task by Kyle Pearce and others. 
I had never done this before, so I decided to try something new and NOT overprepare. Didn’t make a handout, or a structure, or a OneNote page, or a powerpoint, or anything. Just watched the videos myself, made sure I understood the basics of what happened, and wing it.
It kind of worked.

Boat on the River 

Act 1

I told the students “We will watch a short video. Ask whatever questions you can about what you see.”
We watched the act 1 video three or four times and I elicited and wrote down questions.
Result from one class:
Some of the questions were silly (9), some were answerable by watching the video we had more carefully (1, 2, 12, 13ish, 10ish), some were ineffable (16), some required outside knowledge (11, from a student who sails), and MANY were math. I was going for #3, so that’s good. To solve!

Act 2

I showed them the pictures on the PDF provided by the task. 

(There is another image that establishes a scale for these pictures of 1 in = 10 ft). Since we already knew how to work with sine ratios, this was easy for the girls to work with, and they found that the boat will make it… barely. I let them work on this in partnerships for 5 minutes, explain their work, and then…
Act 3
We watched the Act 3 Video and clapped when the boat [spoilers!] made it through.

Overall outcome

A success. I don’t feel like the students were hugely more invested in this than in more “normal” word problems, but if I increase the enthusiasm of my presentation I think we can get there. I do like the basic structure, and calling for questions brings me back to the first steps of the Engineering Design Process from my science teaching days; a useful way to get ideas.

Big Nickel

Since we had never done area problems that required trig, this was a harder, longer task for my students, but that wasn’t the only stumbling block I ran into here.

Act 1

After watching the short video at the top of the task,  my students thought the monument was funny, but their questions were silly, snarky, and didn’t really go where I was hoping. I eventually had to straight up give them the question I was hoping for, the last one.
I don’t know how to inspire the sort of sideways silliness needed to find that question; a perfectly designed 3 act may do a better job of getting them there simply from the video, and maybe this one isn’t perfect. Or maybe I just need a sillier group of students to get there.

Act 2

“What do we need to know?” was actually more interesting here. We needed to know dimensions of the big nickel and the real nickel (height and thickness) and my students pointed out that we also needed to know if it were hollow  or solid – basically is this a problem about surface area or volume! This part was fun, as we scoured the wikipedia entry for the information we needed:
  • It is 9.1 m tall, 0.61 m thick.
  • It is made of steel plates attached to a skeleton – basically hollow!
  • A real nickel is 2.12 cm tall, 1.27 mm thick
I told them this was all they needed and let them loose. We have studied the area and angles of regular polygons before; I told them they may want to remind themselves of those formulas and techniques, as well as look for right triangles to use trig. I would say about 2/3 of each class got very close to finding the area of both the big nickel (in square meters) and the little nickel (in square cm). Some needed more hints and guidance than others. This part was slow, and frustrating, but went well. We got to the end of class and I asked how many nickels it would take to tile the front of the big nickel, and then we had to remember that square meters and square cm are not the same unit – so we got to review square unit conversions again, which we tackled earlier in the year. The math of this was good.

Act 3

… there isn’t really one. We can find other people’s solutions, but unfortunately there is no way to get the kind of satisfactory resolution from this problem that we got from the boat. I think this may actually disqualify this as a true 3-act problem. It is a GOOD problem, but, frankly, nobody has ever built the Big Nickel out of nickels, so we don’t have an answer to the question outside the theoretical.
How do you create the resolution of Act 3 when the problem at hand can’t actually be done?


My first attempt at 3 act problems was fun, and led to good math, but needs work. I think I need to add some structure, and I need to think more about some of the components, specifically how to get the resolution of Act 3 when the problem does not allow for it and how to get the “right” question out of Act 1 when it’s not necessarily obvious (but IS creative and interesting).