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