A couple of days ago, I began the painstaking process of clearing all of this work out in preparation for the new school year. While I would have loved to use them as they are, they no longer fit the game narrative that I am exploring for the upcoming year, plus, I reused Bosses across my grade levels. While this may not be a problem for most teachers, it is for me because I loop the students, so most of them would be re-battling the same bosses they already know. Perhaps they would not remember, but all it would take is one of them to check their student sheets (where the bosses are displayed), and the excitement of getting collecting the boss would be diminished.
With all of this in mind, I started creating new bosses - we will be rescuing scientists - and clearing out the old forms/sheets tweaking the originals a bit, which is what I am sharing today.
Boss Battle Form (will auto copy) - I have dummy-filled it with some data so that it is easier to explain. If you make a copy simply delete all the prefilled contents of "form responses 1"
While it looks basically the same, there are important things to note.
- Column L contains each of the Boss possibilities. Each is its own drawing (published to the web) and placed into the cell using
=image("published address of your google drawing")
You may be tempted to make use of the new "insert image in cell" function. Don't. You will be calling the images into the boss battle pane and into the scores sheet. Google Sheets does not recognize images insterted that way as actual objects so until they fix that what will happen is that you get blank instead of the image you want to display.
- Cell F15 now contains a new formula:
=index('Form Responses 1'!$B:$B, match(true,isnumber(search("/",'Form Responses 1'!$A:$A)),""))
This formula looks at the timestamp looking for the first appearance of the slash symbol (/) in Form Responses 1 column A and reports the matching contents of column B in the same sheet. This formula solves the problem of having to change cell references as the sheet is populated the first time around.
- Cell F16 contains a similar formula:
=index('Form Responses 1'!$B:$B, match(true, 'Form Responses 1'!$C:$C=12, 0))
In this case, it matches the contents in column B with the first time the "perfect score" of 12 is achieved, and of course that can be changed to whatever number you is your "perfect score".
Perfect SheetThat sheet simply reports the contents of first hit and first perfect hit and assigns them a value. I bring that into my leaderboard using the Vlookup function as a way to give students an added incentive.
Formula that imports this into your leaderboard:
=IFERROR(VLOOKUP(A17,IMPORTRANGE("Boss Battle Google Sheet Identifier","Perfect!A:B"),2,0), 0)
Where the number 2 is the column number where those "extra points" are housed.
If you are interested in the Vlookup formula as it pertains to the leaderboard, visit my post "Assign XP automatically using Vlookup - Google Sheets"
Scores Sheet - AKA Where the magic happens
As originally, this is a pivot table that aggregates the data generated first by adding all the scores each student generates (Column B - Sum of Scores) and then by reporting the maximum score the student obtained (Column C - Max Score).
Column D (Counts the number of times a student achieved a Perfect Score)I've been toying with the idea of having students "catch/rescue the Bosses". This collecting of the bosses had a great impact in engaging students to attempt the quizzes multiple times throughout a semester, regardless of whether it is an official Boss Battle day with the whole display of the Boss. In order to achieve this assigning of bosses I first needed to know how many times a student scored a perfect score. To do this, I added a Calculated Field to my Pivot Table:
The Times Perfect Score was added using by clicking Add values and selecting "calculated field". The formula is simply a COUNTIF, though you notice that the normal use of the quotations is eliminated.
=COUNTIF (Score, 12)
The number 12 obviously corresponds to the "perfect score" in this particular case and could be changed to whatever suits your needs.
If you are recreating your own, also make sure that you select Summarize by Custom and Show as Default.
That got me the number of times a student got a perfect score. You may be wondering why this was important. The answer is that I did not want to give out the Boss simply based on the number of points. My students are notorious for finding the easy way out and often would rather take a quiz 100 times scoring 1 each time than getting 100 the first time.
Column F (Where the Shiny Bosses come in)
With that number of "perfect scores" aggregated by student, it was time to move on to the "catching/rescuing" of the boss.
When working to pivot tables, you can still add formulas to the columns that are not being used by the pivot table itself. I added a title to Column F and called it boss obtained. To give the illusion of randomness to the catch, I decided on the following formula
=IF(D2>=10, Boss!$L$9, IF (AND(D2=9), Boss!$L$8, IF (AND(D2<=8,D2>=6),Boss!$L$9, IF (AND(D2<=5,D2>=1),Boss!$L$8," "))))
The formula is just copy/pasted down through the sheet so that each row gets its own student reference. The results of this column are again imported to my Leaderboard using VLOOKUP as mentioned in Perfect Sheet above. The key here is to remember that VLOOKUP references the column number:
=IFERROR(VLOOKUP(A2,IMPORTRANGE("your sheet identifier","scores!$A$2:F"),6,0), 0)
where the initial A2 - is what it is looking for (e-mail of my student),
"scores!$A$2:F"- the sheet and range where the data is housed
6 - really column F where the bosses caught were "assigned"
Column E - Maximum Possible Score: the end to "grinding"
Last year, some of my students greatly inflated their leaderboard standing by retaking boss battles over and over again. While in principle this would be OK with me, there is a point where enough is enough. I know that I can simply close the form, but that leaves the student who may need the extra practice out of the loop, and opening and closing a form manually means that I must remember to do so. Taking this into consideration, I opted for writing in a formula that reports a set number of points out to my leaderboard (again with the VLOOKUP formula) to my leaderboard once the maximum is reached.
=if (B2>40, 40, B2)
That is it for today. I hope this inspires you to keep working on your leaderboards and perhaps make some additions or modifications. And as always a big shout-out to Mr. Powley who although has moved on to standards-based grading and may be letting go of the Dread Sheets inspired this whole Boss Battle process.