Fortnite has been my go to game lately. The way that Epic Game is constantly tweaking and playing around with subtle little motivational techniques is fascinating. A couple nights ago I noticed this interesting change.

Maybe you can’t see it. Let me zoom in.

There we go.

I noticed this as I was playing. The screen said that I was only 3,750XP from moving up to the next level. Not realizing why I did it I started the another game with the thought that I had a bunch of quests that could get me at least 12,000XP and I could easily level up. Once I got into the game and completed the quest I realized what had just happened! Fortnite was subtly convincing me to play just one more game by suggesting that I was really close to leveling up. I also started to notice that most of the Season Long accumulation type quests are mostly hidden until the player gets close to accomplishing the goal. In this way the player is always “just so close” to leveling up and always has a quest that will get them at least most of the the way there! Brilliant!

Of course, I immediately wanted to think about how I could apply that to my classroom. I already have Personal Dashboards for each student. It is just a matter of programming the Google Sheet Formulas. Here is what I came up with!

2021-22 Personal Dashboard Template (view only)

The Basics

This is not elegant. These formulas are brute force and ugly but they work. Mr. Sederquist taught me my new favorite term “hit it with a rock until it works”.

The Number

To get the number the formula needs to know the student’s current XP and the number needed to get to the next level. This is pretty easy to find BUT the challenge was to write a formula that would automatically determine what level the student was at, then determine what number the student needs to get to, AND have that automatically change every time the student’s XP changes!

First step was to create the levels which is found on its on tab called “levels”

Next was the Brute Force Formula…

This is a second tab “Student Data”. Using the XP count in Cell B6 the function in column D is a series of functions that subtract the current XP from each level’s XP count. a negative number means that the level has already been reached. The “IF” statement is there to make sure that there will only be a single positive number.

The IF statement in Column E is =IF(SUM(D9)<0,””,SUM(D9)) and will only display the number in column D if it is a positive number. Since Column D is only displaying the difference of the number at the current level there is only one positive number. BUT to display this number on the student’s page there needs to be a consistent location for the number. This is accomplished through a simple SUM formula in cell E3 that adds all of the numbers in column E.

Finally, a simple formula on the student display shows the number in cell E3.

The Level Bar

The leveling bar is created through this formula =if(D9>0,SPARKLINE(\$B\$6,{“charttype”,”bar”;”color1″,”red”;”max”,Levels!\$A7}),)

Each cell in Column F has this formula. The IF statement forces the program to check Column D to check if the number is positive. If it is a positive number then the SPARKLINE formula begins. This formula displays a bar graph that shows the XP number found in Cell B6 with a full bar representing the XP needed to move up to the next level. The max number on the bar graph is found on the Levels Tab which is the final part of the Sparkline formula. The if statement makes sure that only one bar graph will be displayed.

To display this bar on the student’s page though it also needs a stable cell that can be displayed. To achieve this Cell E3 has a vlookup formula.

Using the stable “XP needed to the next Level” cell in E4, this Vlookup formula searches for that number in a table created by the updating XP needed to the next level numbers in Column D. When it finds that number it displays the image (the bar graph) in the 3rd column (Column F) of that row.

Then, again, it is a simple transfer formula onto the student’s sheet.

Again… Ugly but functional.

Motivational!

The goal of this is to provide that little extra motivation to encourage students to keep working to level up. It also shows the students how their incremental improvements over time can make big educational gains.