If you are into the idea of mastery learning, might you also consider awarding stars of mastery like in the game Angry Birds? When playing Angry Birds obviously I want to show mastery at the 3 star level instead of 1. 1 star means I can move on, 3 means I am really awesome. To replicate this in schools we set the level of mastery and then encourage students to exceed that through higher level thinking skills. Not through doing more problems, but by demonstrating they understand the material at a deeper level.
I have been wanting to visually represent the mastery learning rather than relying on a numerical score.
I figured out I can use a nested if statement or a vlookup table to visually represent the mastery level. I started by going to clikr.com and finding an image of a star and downloading it. I edited the image to have 2 and 3 stars in the same image. I uploaded all 3 images to photobucket.com.
In a page of the spreadsheet I created a table listing 1, 2, 3 and used the formula =image(“url”,1) for each of the 3 images.
Replace the word url with the image url from photobucket.
In a spreadsheet where I would list the students name and assignments I created a column for mastery level and for stars of mastery. Using a vlookup where I reference the table with the images
The first part of the formula =if(F5=”",”" says if what is in cell F5 is blank put nothing. This keeps the stars of mastery image from showing up if the student has not mastered. I am contemplating having a default “not mastered” image instead of a blank.
The second part vlookup(F5,Sheet2!$A$2:$B$4,2)) says to look up the number in cell F5 and compare it to the table on Sheet2. Make sure you use absolute cell referencing with $A$2:$B$4 instead of A2:B4. The 2 means the image is in the 2nd column.
Fill down the formula down the sheet.
Click here for the spreadsheet sample