For teachers, grading quiz results for papers manually is definitely a hard job. Google Docs provides a good way for teachers to create online quizzes for teaching and learning. But teachers, do you know how to grade the quizzes automatically? If you don’t know, this tutorial will give you the details.
Google Docs supports 7 question types: Text, Paragraph text, Multiple choice, Checkboxes, Choose from a list, Scale and Grid. However, only multiple choice and True/False questions created by Google Docs can be graded automatically. This tutorial will show you the steps to grade the multiple-choice quiz with Google Docs. The basic idea here is this:
Set up a Google Doc Form with your questions.
Fill out the Google Form as – the teacher – with the correct answers (creating an ‘answer key’).
Set up a formula in the form results spreadsheet that will mark answers correct or incorrect.
Assign values to correct answers
Tally result values for grades.
Step 1 – Set up the Google Form
Enter your Google Docs account, and create a new form. Proceed by entering the questions and possible answers. Be sure to save your progress periodically, or at least double check that Google is doing it for you – in the upper right corner of the Google Forms window. Step 2 – Enter answer key
To simplify the grading work, take the quiz yourself before any students take the quiz. Enter the right answers for the quiz and then click Submit. Step 3 – Invite students to take the quiz
Use the ‘Share’ button to enter students, or groups email addresses, or use the published URL for the form to direct students to respond. Step 4 – Using Formula to grade the quiz This is the hardest part of this process…if you would like me to help or conduct a hands on tutorial, please let me know.
1. Now go back to your Google Docs. You will see your quiz as one of your saved documents – actually a spreadsheet. Click on it and you’ll find all responses in a spreadsheet that has your questions across the top, and the responses listed in the subsequent rows.
Column A shows the date and time of each response, you can view when the quiz-takers take the quiz. With our School’s Google Apps account, we may be able to lock access to students who are signed in to their email, but this is something I will need to test.
2. Grade each question with IF formula
The IF formula is similar with Excel, the syntax for the IF function is:=IF ( test, then_value, otherwise_value ) test – a value or expression that is tested to see if it is true or false.
then_value – the value that is displayed if then_value is true.
otherwise_value – the value that is displayed if then_value is false.
Input question 1, 2 and 3 by sequence to column F, G and H to store the score for question 1, 2 and 3.
In cell F3, enter “=if($C3=$C$2,100,0)”, and then select and drag the cell to the end of the question column7. You will see the score of question 1 for each student is graded automatically.
Then, enter “=if($D3=$D$2,100,0)” and “=if($E3=$E$2,100,0)” to G3 and H3, and do the same steps as F3. After these, you will view the score of each question for each student.
3. Summarize the score for each student using auto-sum or total.
Input Total Score to column I to store the total score for each student.
In cell I3, enter “=sum(F3,G3,H3)” to calculate the total score for each student, and then select and drag I3 to I7. Each student’s score for taking the quiz is shown on column I. You saved all that time for grading the quiz with the Formula! Step 4 – Export the quiz score
Google Docs lets you download the quiz score spreadsheet as CSV, HTML, Text, Excel, OpenOffice and PDF format. By clicking File -> Download as, you will get the score report with ease.
Save these reports as needed, and then enter grades into Netclassroom.
You also can view the summary with graphs charting. Particularly for knowing how much knowledge students master, the summary response is great – especially since you can always click “see complete responses” and have them available to you immediately.
Google Docs supports 7 question types: Text, Paragraph text, Multiple choice, Checkboxes, Choose from a list, Scale and Grid. However, only multiple choice and True/False questions created by Google Docs can be graded automatically. This tutorial will show you the steps to grade the multiple-choice quiz with Google Docs. The basic idea here is this:
- Set up a Google Doc Form with your questions.
- Fill out the Google Form as – the teacher – with the correct answers (creating an ‘answer key’).
- Set up a formula in the form results spreadsheet that will mark answers correct or incorrect.
- Assign values to correct answers
- Tally result values for grades.
Step 1 – Set up the Google FormEnter your Google Docs account, and create a new form. Proceed by entering the questions and possible answers. Be sure to save your progress periodically, or at least double check that Google is doing it for you – in the upper right corner of the Google Forms window.
Step 2 – Enter answer key
To simplify the grading work, take the quiz yourself before any students take the quiz. Enter the right answers for the quiz and then click Submit.
Step 3 – Invite students to take the quiz
Use the ‘Share’ button to enter students, or groups email addresses, or use the published URL for the form to direct students to respond.
Step 4 – Using Formula to grade the quiz
This is the hardest part of this process…if you would like me to help or conduct a hands on tutorial, please let me know.
1. Now go back to your Google Docs. You will see your quiz as one of your saved documents – actually a spreadsheet. Click on it and you’ll find all responses in a spreadsheet that has your questions across the top, and the responses listed in the subsequent rows.
Column A shows the date and time of each response, you can view when the quiz-takers take the quiz. With our School’s Google Apps account, we may be able to lock access to students who are signed in to their email, but this is something I will need to test.
2. Grade each question with IF formula
- The IF formula is similar with Excel, the syntax for the IF function is:=IF ( test, then_value, otherwise_value ) test – a value or expression that is tested to see if it is true or false.
- Input question 1, 2 and 3 by sequence to column F, G and H to store the score for question 1, 2 and 3.
- In cell F3, enter “=if($C3=$C$2,100,0)”, and then select and drag the cell to the end of the question column7. You will see the score of question 1 for each student is graded automatically.
- Then, enter “=if($D3=$D$2,100,0)” and “=if($E3=$E$2,100,0)” to G3 and H3, and do the same steps as F3. After these, you will view the score of each question for each student.
3. Summarize the score for each student using auto-sum or total.then_value – the value that is displayed if then_value is true.
otherwise_value – the value that is displayed if then_value is false.
Input Total Score to column I to store the total score for each student.
In cell I3, enter “=sum(F3,G3,H3)” to calculate the total score for each student, and then select and drag I3 to I7. Each student’s score for taking the quiz is shown on column I. You saved all that time for grading the quiz with the Formula!
Step 4 – Export the quiz score
Google Docs lets you download the quiz score spreadsheet as CSV, HTML, Text, Excel, OpenOffice and PDF format. By clicking File -> Download as, you will get the score report with ease.
Save these reports as needed, and then enter grades into Netclassroom.
You also can view the summary with graphs charting. Particularly for knowing how much knowledge students master, the summary response is great – especially since you can always click “see complete responses” and have them available to you immediately.