This is Part Two of a three part series on making quizzes with Google Forms. Part One focused on how to make the quiz. Part Two (this one) focuses on how to set up automatic grading using Google Spreadsheets. Part three will focus on distribution of the quiz.
If you just want a good automated add on to Sheets, try Flubaroo. However, if you like the DIY approach, read on.
Setting up automatic grading
The simplest way to set up automatic grading for your quiz, is to first take the quiz yourself putting in all the correct answers, and then doing a little work with the Google Spreadsheet where your quiz results are going to be stored. If you have a “name” input option for your student quiz, you can type “Answer Key” to identify your answers later.
After answering the questions correctly, return to your Google Form, and select “View responses” from the menu bar.
You should see a Google Spreadsheet which shows the answer key you just made.
If you are familiar with Excel or Numbers, Google Spreadsheet should look somewhat familiar. Now comes the fun part. We will write one tiny bit of spreadsheet code, and cut and paste it into our spreadsheet to make this quiz self-grading.
Click the “Form Responses” label at the bottom left corner of the spreadsheet, and select “Duplicate.”
Ignoring the Timestamp and Name columns, select the first answer cell. (This should be row “3”, column “C”)
Copy and paste this code into the cell:
=if('Form Responses'!C3='Form Responses'!C$2,1,0)
If properly executed, you should see a 0 in the cell, because there is no answer yet, so the cell is currently “Incorrect.”
Now, cut and paste this cell, into the entire row using the right click, copy function.
Click and drag to highlight the cells.
Right click, “paste.”
You should see all zeros.
You do not need to understand this code, if you are not interested, but you do need to know how to manipulate it to suit your purposes. The last two numbers is all you will need to change.
The last two numbers are for the point value if the answer is correct, and the point value if incorrect.
The default values for this formula are 1 point for a correct answer and 0 for an incorrect answer. Since it is unlikely you will want to reward points for an incorrect answer, all that is left to be determined is the value of a correct answer.
If you would like to change the value of a correct answer, change the second to last number in the formula 1 to the value you desire.
…, 1, 0) → …, 3, 0)
Do this for each column as you wish.
When you are finished, cut and paste the entire row, down as far as you think you will need based on the number of students you will have answering the quiz.
The last step is to create a new column at the end which will tally the results. Click in the cell to the top right of the grading area, and title it SCORE.
Copy this formula into the first answer cell below it row 3.
When you hit enter you should see #N/A.
Double click the cell, and single click in between the to the parenthesis marks. ( )
We want this cell to calculate the sum of the cells to the left, so while the cursor is in the parenthesis, click and drag to highlight the cells you want to be calculated.
You should get 0, because there are no points earned yet.
Now, Copy and Paste the cell into the cells below it to create your totals column.
Congratulations! Your quiz is now ready to share with your students. When you are ready, move onto Part Three: Sharing a Quiz with Students.