I’ve been trying to find ways to get students to play 1 on 1 review games against me while in a whole group setting. This Spreadsheets “ImportRange”based trick seems to be the way to do so. It also allows for Personalization in Opponents and in the Student’s “powers”.
The base of the game is the classic Yahtzee dice game. Some kids had to be trained on how to play it but creating the score cards and all of the calculations in a spreadsheet was fairly easy. From there it was a matter of linking the “Boss Sheet” which I use to individual student spreadsheets where they enter their scores.
The Boss Sheet
The Boss Sheet is where the teacher enters their scores, can check who is victorious and who lost, and can check to make sure there aren’t any seriously nefarious score entries. It is bland but effective.
Check it out here
The true power though is that the Boss Sheet scores feed into the Student’s Match Sheet where it used to make 4 possible opponents.
Student Match Sheet
You can access the Student Sheet with this button. The boss sheet and student sheet copies are paired so you can see where they are linked.
Because this is WWE Themed the students can pick to fight one of four opponents – each has unique strengths and weaknesses.
By selecting one of the opponents that triggers a nested “If:Then” formula that leads to a different Boss scorecard on the student’s Match Sheet. This is from the “Formulas” Tab and uses the name in the dropdown menu on the Match Page to look at the total score in the “Bosses” Tab.
Note the nested “IF:THEN” in Cell A2.
Here is the “Bosses” Tab. Well, this is the Alexa Bliss score card. The other four opponents are next to this scorecard.
Cells C2 and F2 are importrange formulas that are pulling the rolls from the boss sheet. (Cell A1 is the link for the Boss Sheet.) By importing just the scores and not the entire sheet I can play with the scoring calculations. For example, the importanted scores in C3 through C8 get a 2X Multiplier, calculated in the highlighted yellow cells. She doesn’t get any Chance score though so that is just removed from the calculation in F13.
The Boss Score for the correct opponent is pulled from this tab and then added to the Formulas sheet. Then from the Formulas sheet is is added to the “Match Page” tab. (Cell A5 below)
The student scoring works the same way. They select their wrestling type, click on the appropriate tab and then enter their scores. The bonuses and multipliers are are pre-calculated formulas and are added as they enter the scores.
Review Game Mechanics
We used Plickers to collect the data.
If a student answered correctly they were able to roll their dice. They get 3 rolls and have to add their score by the end of the 3rd dice roll. If they answered incorrectly they lost their turn.
There are 15 possible moves on this score card. I asked 16 questions. If students got all 16 correct they had the option to “swap” a new roll with an old one.
As the “Boss”, I rolled every time. I built enough weaknesses into the Bosses so that the matches were surprisingly fair. In one class we had about a 50/50 student/boss victory and in the other 4 classes the students won at a slightly higher rate.
Thas been a pretty popular game. Students enjoy the dice rolling. A few students have never heard of Yahtzee so it takes 3 or 4 rounds and a few questions before they get the game but once they got it they had a blast.