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

Today’s ridiculous project – a google sheet for AP style computer output

Working through my fourth candy assignment today, we ran into a problem where we could not easily create AP Stat style “computer output” for regression with student data. We do not have licenses for Minitab or JMP, R is outside my sphere (and the output looks different anyway), and Geogebra, statskey, and other free options I have tried either give different data, confusing data, or not enough data. I thought NCTMs Core Math Tools did what I needed, but I misread the output when planning.

I have since learned that StatsCrunch can output very similar data (and is nice enough that I may do subscriptions for it next year and use it more thoroughly with my students), but I did not know that tool this morning.

So what did I do? Made a google sheet that you can paste data into OR enter information for another google sheet (url, tab name, etc) and it will generate computer output for you. I may not ever use it again now that I have figured out StatsCrunch, but for data already in a google sheet this is quite possibly easier. I put it here for anybody who may find it useful.


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.