Online College Courses for Credit

2 Tutorials that teach Tracking Gamification Data Using a Spreadsheet
Take your pick:
Tracking Gamification Data Using a Spreadsheet

Tracking Gamification Data Using a Spreadsheet

Author: Jody Waltman

In this lesson, you will learn how to build a spreadsheet to track gamification performance data in the classroom

See More

Like what you're learning?

Personalized Learning through Gaming

Take the whole course from Capella University FOR FREE


Video Transcription

Download PDF

In this tutorial, we'll learn how to track gamification data using a spreadsheet. We'll learn about three different approaches to gamified grading. We'll explain some methods for calculating grades in the gamified classroom. We'll explore methods for implementing leaderboards. And we'll see how to build a spreadsheet and leaderboard for use in your gamified classroom. Let's get started.

First, let's look at three different approaches to gamified grading. Approach one is to replace grades with experience points. In this approach, students earn these experience points as they accomplish goals and as they move through various levels of mastery. The expectations for earning experience points must be clearly explained to the students in terms that they understand.

The second approach is to use a combination of traditional grades and experience points. In this method, the teacher tracks experience points in three different areas given game style names-- student work, teamwork, and simulations. In this method, high point values should be used as they typically are more motivating than low point values. Progress in earning experience points can be tracked on a leaderboard.

It's important to note that experience points can be accumulated, but should never be deducted from a student's score. In this approach to grading, more traditional grading methods are used for tests and quizzes. Although it's logical to conclude that students who do well in the gamification aspects of the classroom are also going to be likely to perform well on tests and quizzes because of the effort that they have put into the practice of the skills through the gamification activities.

If you're using a leaderboard to encourage healthy competition among your classes and to make the learning more visible, students may even choose to use their gamer tag names instead of their real names, even on the more traditional tests and quizzes. What might be the rationale for separating the traditional grades and the experience points?

Well, the leaderboards that track experience points can be public, and these experience points can be awarded in order to motivate and inspire kids to progress along the storyline or narrative that's created for the class. Meanwhile, private grades not shared with the rest of the class can be assigned for required or expected work like tests and quizzes. Students can be motivated to earn even more experience points by taking on side quests. Earning more experience points leads to earning more badges, and also to greater mastery of skills and abilities.

The third and final approach is to track experience points and then turn those experience points into grades. In this approach, points are assigned to each game level and at the end of the marking period, these experience points are translated into traditional grades. This approach may be more motivating than traditional grading and averaging since students can watch their grades increase as they accumulate experience points over the course of the grading term. Remember that students can fail to accumulate experience points, but these points are never deducted from a student's overall total.

What might grade calculation look like in the gamified classroom? Since gamification is built on the ideas of mastery and competencies, points are accumulated rather than averaged. It's important to include both mastery and progress when you're calculating a student's grade. Here's an example of how a student's grade might be calculated.

The mastery grade, which reflects student progress on individual objectives might be averaged and then weighted at 50%. In this portion of the grade, objectives that students do not meet are not entered in as a 0. Only mastered objectives are used to calculate this portion of the grade.

The progress grade, however, does take into account the objectives that students have not mastered. The more objectives that the student completes, the higher their experience points total will be. So at the end of the grading term, weight each student's experience points total at 50% as well.

Here's a simplified example involving just four objectives. Note that for the mastery portion of the grade, on each objective the student could earn up to 100 points. Objective four was not mastered, but in the mastery portion of the grade objectives that are not mastered do not count against students. So this student's three scores of 85, 90, and 75 out of 100 averaged to 83%. Weighted at 50% of the total grade, this gives us 41.5%.

Now on to the progress portion of the grade. Notice that the total possible experience points were not the same for all of the objectives. On objective one, for example, this student earned 40 experience points of the total possible 50. On objective 2, 25 experience points were possible and the student earned them all.

Objective four, which was not mastered, earned the student 0 experience points out of a possible 50. Overall, the student earned 165 experience points out of the possible 225, which calculates to 73%. This score weighted at 50% gives us 36.5%. The two bold totals, 41.5% and 36.5%, add up to a final grade of 78% for this student for this grading term.

Now that we've seen how to implement experience points into the overall grading scheme, let's take a look at how use leaderboards to track student progress throughout the course of the grading period. There are three different types of leaderboards that can be used to track experience points accumulation. An individual leaderboard displays the high XP scores for individual students across one or more class periods. A team leader board displays the combined scores for teams that are set up within the classes. And a class leaderboard displays class XP totals, which allows for some friendly competition between classes.

When implementing gamification, it's important to build a spreadsheet that you can use to easily track students' experience points. Note that Mr. Matera's spreadsheet is actually available for purchase online, but you can also use Google Sheets to build your own spreadsheet. This spreadsheet can then be shared with students and viewed collaboratively in order to track team and class progress.

To create your own spreadsheet, open Google Sheets and click the green circle with the plus sign to create a new spreadsheet. Change the name of the spreadsheet if you wish. Create the following column headings. Name, Total XP or experience points, Team Name, XP Percent, and then create column headings for all of the tasks that students will complete throughout the grading period.

In the second row of the spreadsheet, you will keep track of the total possible XP that students can earn. Create a row heading for row two using the phrase Possible XP. You may wish to also use a different background color for the cells in row two. From the View menu, freeze the top two rows of the spreadsheet.

Now we'll write a formula that tells the spreadsheet to add up all of the XP scores for the first student on the list. Notice that the range of cells that I indicated is from cell E3 to cell ZZ3. If you have created your complete list of tasks, you'll know exactly which cell you need to end with. Now copy and paste the formula to the rest of column B.

Next, we'll tell the spreadsheet to divide each student's total XP by the total possible XP found in cell B2. Unfortunately, since we're dividing by the same cell value each time, this formula can't be simply copied and pasted. Type in the formula that is unique to each student in the list.

We also need to create a formula to fill in cell B2 with the sum of all of the possible XP values from all of the tasks through the grading period. Now, assign total possible XP values for all of the tasks on the list. Have students select gaming names and enter these names in column A.

Finally, let's see what the spreadsheet looks like with some student XP scores entered in. At any time, you can sort the sheet by student name, team name, XP percentage, or any other factor that you wish.

In this tutorial, we learned about three different approaches to gamified grading. We saw an example of calculating grades in a gamified classroom, and we talked about implementing leaderboards to track experience point accumulation. Finally, we saw how to build a spreadsheet and leaderboard that can be used to track progress in your gamified classroom.

Here's a chance for you to stop and reflect. Are you confident in your ability to use Google Sheets to build a spreadsheet that could be used to track experience points in your classroom? Can you see experience points fitting in with the grading system that you currently use in your classroom?

To dive a little deeper and learn how to apply this information, be sure to check out the additional resources section associated with this video. This is where you'll find links targeted toward helping you discover more ways to apply this course material. Thanks for joining me today. Have a great day.

Notes on "Tracking Gamification Data Using a Spreadsheet"

(00:00 - 00:24) Introduction

(00:25 - 03:09) 3 Approaches to Gamified Grading

(03:10 - 05:24) Calculating Grades

(05:25 - 05:59) Implementing Leaderboards

(06:00 - 08:30) Building a Spreadsheet

(08:31 - 08:52) Review

(08:53 - 09:26) Stop and Reflect

Additional Resources

Gamify Your Class Level I: XP Grading System

This is a great post that demonstrates how to use the level up (XP) system to track progress in the gamified classroom. This post provides a clear how-to for teachers interested in using XP points as part of their grading practices in a classroom based upon gamification.