Monday, March 31, 2014

Rubrics in Google Spreadsheets

I have been searching for a way to create and use rubrics through Google apps. I know there are scripts out there like Doctopus and Goobric, but I have yet to get the scripts to run and present results as intented. Even after following tutorials and guides. I thought, there has got to be a way to use the spreadsheet formulas to do what these scripts attempt to do. This method seems to work well for me, maybe some of you who are not sold on using Google Scripts yet might find this useful.
Here's what I did, and really like the way it works.

  1. I created a spreadsheet for my rubric
  2. I entered in the appropriate headings and descriptors, to make it look like a rubric.
  3. Here's the technical stuff. I added a formula and conditional formatting to account for some changes.
    1.   I added a column titled "total" 
    2. I put this formula into the first cell and copied it down the column. =if(right(B3)="@",4, if(right(C3)="@",3, if(right(D3)="@",2, if(right(E3)="@",1,0))))
    3. all this formula says is that if the @ symbol is to the right of all the text in the referenced cell, then apply the value 4,3,2, or 1. If it is not there, apply the value 0.
    4. Further down the spreadsheet there is a small formula to calulate a total and adjust the score out of a 100%.
    5. This cell uses the following formula ="Grade: "&(Sum(F1:F23)/80)*100 &"%"
    6. which tells the spreadsheet to add the text Grade, calculate a percentage of teh earned values, and attach a % symbol to the returned value. This is only for ease of student viewing. As I collect the values for my grading purposes I would use the raw tally for the total available.
  4. Below is a copy of my rubric Used for grading Lab Reports. To get a full feel of the spreadsheet you will need to "make a copy" to click around in the cells and play with the formulas.
  5. The last detail is to add the conditional formatting option to highlight the appropriate cell a specific color. You can see that the first box of the rubric is purple, because it contains the @ symbol at the end of the text, which also triggers the formula to apply 4 points to the total.

Again, I know goobric will do this automatically, but the rules for which you need to "assign" work sometimes don't' match with they way that I have students doing assignments. I simply fill out the rubric then click print, and select save as pdf, and change the destination folder to the synced Google drive folder for that student.

No comments:

Post a Comment