Badges are a good way to indicate a student’s progress. In my experience though a private badge is useless. There needs to be some sort of public display. I believe this motivation ties into the SAPS reward of Status – a powerful and engaging reward. I like to tie the badges to classroom powerups as well which provides both a sense of immediate Purpose (on top of the more abstract long term concept of History’s real world applications) as well as an Access reward. So as part of the Marvel Power Grid Badging system I want to create both a physical AND digital display system. The Physical will be on display within the classroom while the digital will be kept on the student’s Personal Dashboards and can be shared with friends and family outside of the classroom. This will be a 2 part series with today’s post showing the Personal Dashboard Digital Display and how I have automated the system through Google Sheets.
The Foundation
The basics of the Power Grid Display system starts with my Google Sheets based Gradebook. Click the link for more details, but the relevant part of the gradebook is a new tab that I called Items (which is not really a great name, but I don’t want to rename because it is how I referred to the tab in all of the Dashboard formulas).
Students’ XP grades are added on the “Grade Records” tab but the Badging/Power Grid Boosts/Upgrades will be added on this Items Tab.
As students earn upgrades in the Power Categories then I can simply add a “1” to the appropriate column. For example, if a student were to rank up in the Mental Power category to level 2 I would add a number 1 to the Mental Powers 2 category. All students will start at level 1 on the Power Grid though to indicate “normal” powered in that category.
Their initial Power Grid on the main dashboard page will look like this…
…and on the Power Grid Tab it will look like this.
As they move up in the power rankings an energy beam will indicate their status. It looks like this:
and this
The Main Page grid is meant to be a quick hitter to let students see their progress. The Power Grid tab shows the various powers associated with moving up the power rankings. Most of the powers deal with various battle mechanics or XP multipliers. This isn’t a finalized list but is a good starting point. I might add some more variation for different possible play mechanics – moving a step closer on a throw, instant redo of a physical challenge, etc…
The Formulas
Most of the automation is done through Google Sheets’ Importrange Function and then some nested IF statements.
The first step was to import the “Items” tab from the Gradebook into the dashboard. In the picture below Cell A1 is the link to the Gradebook Google Sheet. The formula in Cell A2 is pulling the header information from the Gradebook Items tab and then transposing that information into a vertical table. In Cell B2 I have the same formula with the imported row changed to match the student’s specific data. Column D has the function =image(_) which will pull in the image from the published Google Draw in the Cell next to it. So for example, the Cell D6 formula is =image(E6).
The real magic is Column C though. In the final row of each Power I have a simple =sum function to add up the total number of points in each power category. So in Cell C11 you can see that this student has 3 points towards the Mental Powers category. When paired with a Nested IF statement, depending on the number in column C, 2 different pictures could possibly be displayed in a single cell.
With the function =if(Boosts!C11=2,Boosts!D8, if(Boosts!C11>2,Boosts!D7) either the picture in D7 or D8 will be show in the cell. If C11 is exactly 2 then D8 will be shown. Since this is the cell showing Level 2 of the Mental Powers category this means that this is the “End” of the beam and the end of the Energy beam will be displayed. If this is not the end of the beam the student’s Level will be higher then the number in Cell 11 should be greater than “2” thus the 2nd IF statement which would display D7, a section of the beam.
Notice the Nested IF formula in Cell B5 below.
The same principle is applied to the main page Power Grid but the pictures are turned horizontally and the nested if statement is slightly different. This is the formula in cell G3 – Mental Powers Level 2.
To update these Levels on the spreadsheet I just need to update the Items Tab back in the Gradebook!
Classroom Powerups 