*update – The temptations idea was from Jennifer Ludlow (@MrsLudlow6Eng). Ms Perky seems to have a similar system. Both are good follows!
In one of the Twitter Chats I participate in someone brought up a really interesting mechanic referred to as a “Temptation”. I am pretty sure this was brought up by DianaLyn Perkins (@PerkyScience) but we can’t remember so if this is someone else’s idea please let me know so I can give you some credit. Anyways, I really like the idea.
Essentially a Temptation was an event for a single student in a guild. The temptation would be sent out at an inopportune time, like during a Boss Battle, as a distraction. If the student took up the temptation they would lose time and the ability to help the guild BUT if the temptation was successfully accomplished they might receive a powerful tool to help the guild later. When I heard the idea it was right after seeing Avengers: Infinity Wars and (spoilers?) I was reminded of Thor leaving the main story line in order to get a new super powerful hammer. Sure, Thor wasn’t with the rest of the Avengers battling Thanos but if he succeeded in getting the new hammer it would potentially swing the battle to Avengers.
I loved the concept but during the school year it would be another mechanic to think about and track. Knowing my own tendencies and how easy it is to forget some of these cool mechanics in the heat of the semester I wanted to figure out a way to automate the process and let the students take responsibility for completing the Temptation. The question was then how to create a system where the students would trigger the assignment, get the Temptation Activity, and then receive the reward.
I have figured out a way to automate the process with 10 different temptations! I think this fits in well with the Hero’s Journey narrative since after “Receiving the Call” the Hero is often tempted. Sometimes the Hero avoids the temptation but other times the Hero succumbs and has to overcome some difficulty caused by the moment of weakness. Often they come out stronger for having failed and overcome the obstacle. My narrative is based on a Super Hero theme so this makes a lot of sense. Below I have included my system for administering the through my gamified grade book and the student’s personal grade sheets. Each student will have their own grade sheet so it will literally be an individual temptation that is open only to that student at random times.
Step 1 – Personal Grade Sheets (or a student accessible spreadsheet)
I have talked about my Personal Grade Sheets before when discussing the XP Grading System. Essentially, I have linked my Gamified gradebook to an individualized Student Grade Sheet which automatically updates when new grades are entered. Here is what 2018-19’s Sheet looks like!
The student information and XP count per assignment is down the left side. The Avatar Type symbol and cards as well as the student’s current level and any ranking pass codes earned are along the top. The “Temptations” codes and formulas are hidden in the black space below the cards! I will show you how to do this below.
Step 2 Writing Nested IF statements
There is probably a more elegant way to do this but for a layperson like myself the nested IF statements are just magical. A nested IF statement will return different information into a cell depending on the IF statement that is true. This sounds complicated but it is fairly straight forward. For example, in the picture below IF cell D14 contains the number 100 cell D12 will display the word “Temptations” and D13 will display a URL I have programmed. IF cell D14 does not contain a 100, say for example it says 101, then both cells will remain blank.
When the randomized number pops up in cell D14 then the information will look like this.
I will explain how to change the cell fill color from Black to Red in a few steps.
The formula statement that will return these results are Nested IF statements. Lets start with cell D12 – the “Temptations” heading:
=IF(D14=100,“Temptations”,IF(D14=200,“Temptations”,IF(D14=300,“Temptations”,IF(D14=400,“Temptations”,IF(D14=500,“Temptations”,IF(D14=600,“Temptations”,IF(D14=700,“Temptations”,IF(D14=800,“Temptations”,IF(D14=900,“Temptations”,IF(D14=1000,“Temptations”,))))))))))
I want the cell to remain blank if there is the numbers 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000 are not in the cell D14. Since I have 10 temptations this means that students have a 1% chance of getting a temptation whenever they log on to check their grades. (If my math is off let me know). I don’t know if this is the correct ratio but it is a full year course so I figure this is a decent chance. I might need to adjust this. Anyways, a single IF statement would be
=IF(D14=100,”Temptations”)
The nesting occurs by adding a second parenthesis
=IF(D14=100,”Temptations”,IF(D14=200,”Temptations”))
You just keep adding nesting IF statements by adding a comma then adding the new if statement.
The statements I wrote here as examples would return the word “FALSE”. The return a blank cell (no text) add a comma to the end of the last statement.
=IF(D14=100,”Temptations”,)
(See the comma after “Temptations”?) This will be important later when I explain how to change the cell colors.
The second nested IF.
This one is a bit more complicated. I wanted to return a URL address that students could click on if they accept the Temptation. I also wanted to change the URL addresses if I made new temptations. To do this I made a table on a new TAB called Temptations.
The other nice thing about the URL Addresses is that I can change the information on the website too.
I also did not change name these websites Temptation 1 through 10. I didn’t want the students to guess the web addresses. Once I am finished tweaking this mechanic I will also right click on the tab and “Hide” it.
Back to the second nested IF statement. As I said this one is more complicated because it will first look at the “Temptations” TAB and then a different cell depending on the number in cell D14.
=IF(D14=100,Temptations!A1,IF(D14=200,Temptations!A2,IF(D14=300,Temptations!A3,IF(D14=400,Temptations!A4,IF(D14=500,Temptations!A5,IF(D14=600,Temptations!A6,IF(D14=700,Temptations!A7,IF(D14=800,Temptations!A8,IF(D14=900,Temptations!A9,IF(D14=1000,Temptations!A10,))))))))))
In this formula if cell D14 equals 100 it will return the information from the Temptations Tab Cell A1. If cell D14 equals 200 then it will be from the Temptations Tab A2. (The exclamation point tells the formula to look in the Temptations tab)
Step 3 THE RANDOM NUMBER GENERATOR MAGIC!
The magic of this nested IF statement is the Random Number Generator. I have put the =RANDBETWEEN function into cell D14 which will generate a random number between 1 and 1000 every time a new action is taken in the spreadsheet.
=RANDBETWEEN(1,1000)
Both Nested IF are dependent on the number that is generated by this function. If the number is a multiple of 100 then the word “Temptations” will appear in cell D12 and the appropriate URL Address will appear in cell D13.
To change the range of the random number simply change the numbers in the parenthesis. For example to generate a number between 100 and 300 the formula would be =RANDBETWEEN(100,300).
Step 4 Hiding the Functions – Conditional Formatting
In order to “Hide” the functions the cell is filled with black and has black text. I want the Temptation to POP OUT when it appears on the students page though so the Conditional Formatting ability is used to turn the background Red. To create a conditional formatting right click on the cell and scroll to the bottom where it says “conditional formatting”.
It will then present you with options to format the cell.
After clicking the conditional formatting it will bring up the menu along the right hand side of the sheet. To create the black background if the cell is empty select “cell is empty” from the “Format cell if…” drop down menu. Then select “Formatting Style” custom from that drop down menu. From there select the empty cell color you would like.
To change the color if the cell contains text select “add another rule” and then change the “Format cells if…” drop down to “Cell is NOT empty”. Then select “Formatting Style” custom from that drop down menu and select the cell color you would like when the text appears.
The example above was done for cell D11 and will look like this.
Step 5 Hiding (and protecting) The Work
The black text of the Functions will all be hidden in the black fill. Even if the students figure this out the nested IF statements only refer to a cell in the Temptations Tab and not directly to the URL addresses. The Temptations Tab itself will be hidden. Just to be extra careful I have hidden Column A in the Temptations Tab as well.
The last step is to make sure that the sheet is protected from any other users. To do this click on the little carrot on the right of the tab. Click on the Protect Sheet and then select the appropriate setting. This will let only select users change the sheet.
Step 6 PLAY! TINKER! GET FIERO!
I am sure that there are more ways to use the Random Number Generator and Nested IF Statements. One way is to use the VLOOKUP Function I wrote about Here (The Magic of VLOOKUP: G.Sheets, Boss Fights, and Badges) and here (More VLOOKUP Magic: A Half-Baked Random Generation G.Sheet Trick).
This is the tip of the Iceberg though!
Get that feeling of FIERO – That moment when you finally type in that statement correctly or solve some problem and through your arms in the air and shout YES!!! Please play with these functions and then show me your moment of Fiero!
Classroom Powerups 