On occasion I have observed some rubrics being utilized to create numerical grades that do not correspond to the rubric scale.
For example on a 3 point rubric scale
3 = excellent
2 = average
1 = poor
A score of 2 being average but a 2/3 = 66% is not an average score.
Since I like spreadsheets I like to do my rubric grading I created a video to show how to create a formula to grade rubrics.
Here is a link to a template for writing a formula. View the Rubric tab and double click on cells to view the sample formulas.
Here are my steps
- Have students fill out a Google Form to submit their First Name, Last Name, Email Address and possibly link to their assignment if it is digital.
- This information will go to a spreadsheet (note in the new Google Forms you have to specify that you want the information to go to a spreadsheet)
- In the column next to the students names make columns for
- Overall Grade
- Each element of the rubric
- Note to the student
- Repeat the columns for each element but to not name them the same.
- Put in default scores for each category and copy and paste down the spreadsheet.
- In the columns that will translate the rubric score you will want to use a nested if statement.
I used in cell I2: =if(E2=3,100,if(E2=2,70,if(E2=1,50,0)))- if the score is 3 give the person 100%, if the score is 2 give the person 70% and if the score is a 1 give the person a 50%, otherwise give them a zero.
- Copy and paste the formula to the right for each of the rubric columns.
- Copy and paste those formulas down for all students in the list.
- Your grade will now reference the columns that convert the rubric score to the percentages you determined.
- For the grade itself, determine how you would weight each rubric score and multiply that by each column percentage.
I used in cell D2: =I2*.25+J2*.5+K2*.25- 25% of the score is the first rubric item, 50% of the score is the second rubric item, and 25% of the score is the third rubric item.
- Insert the valmerge script if you want to email back feedback to the students.
- Insert->Script
- Search valmerge
- You may want to click on the help link to find the sample valmerge template.
- Install the script
- Create a new tab
- Copy and paste the valmerge information
- Fill out the script information.
- I have a column for send. I wanted the formula to say “y” if I had graded the assignment and be blank if I had not.
=if(H2=””,””,”y”) -
- If the note field is blank do nothing, otherwise put a y.
- Notice the quotations, all text needs to be in quotations
