A Simpler Leaderboard

The other day a teacher was looking for an easy way to display a leaderboard for her students. You may recall a previous blog post describing a somewhat complicated (but cool IMHO) way that we set up a leaderboard for Scratch Day 2013.

Of course a leaderboard isn't all you need for a gamified classroom, but it might be part of what you want.

So again we'll use a Google Spreadsheet and publish part of it so that students will be able to see the "levels" that they, and others, have achieved, but they won't be able to see the points that you've awarded.

Create a new spreadsheet and name it whatever you'd like.

On the first line, label the columns "Name", "Points", and "Level". Then leave a blank line and put in the names (or pseudonyms) of the participants.

To the right of that (starting in cell D1), title the rows "Maximum Value" and "Level". Decide on the names for the levels and the maximum values, but you can always change those later.

In cell C3 (the third cell down in the "Level" column) paste in the following formula:

=if(B3<=$E$1,$E$2,if(B3<=$F$1,$F$2,if(B3<=$G$1,$G$2,if(B3<=$H$1,$H$2,if(B3<=$I$1,$I$2,if(B3<=$J$1,$J$2,$K$2))))))

Then press enter and place your mouse cursor at the bottom right of cell C3 (where you just pasted the formula). Click and drag it down in order to fill that formula in for the rest of the column.

When you're done, it should look like this:

Now when you change the points value, it will automatically change the "Level". If you don't mind students seeing the "points" values then you can just share the spreadsheet with them as viewers and you're done.

However if you want to allow the participants to see the "levels" but not the "points", then you need to create another sheet that you can publish. Click the + sign at the bottom left to add another sheet.

Open that new sheet by clicking on "Sheet2".

In cell A1 of the new sheet, paste or type  =Sheet1!A1  and press enter so that cell A1 in this sheet will display the contents of cell A1 in the other sheet.

Again, click and drag from the bottom right corner of cell A1 to fill in the formula for the rest of the column. Do the same for cell B1, but use the formula  =Sheet1!C1  so that it will display the contents of cell C1 from the other sheet. Fill down again, and it should look like this:

Now publish just Sheet2 and share the link with your participants by posting it on your website or LMS.

Sorry, that was a little more complicated than I initially though, but you can do it. If you want to see the spreadsheet that I used for this post, click this link.

Let me know if it works for you.