As technology becomes more infused into every profession, some of us who have been in the recreation field for “a while” find ourselves having to explore the wonders of technology on our own. The problem is that we don’t know what we don’t know. This post is part of a series about technology tools I have stumbled upon that have proven to be the most useful in my job.
I was intrigued with the idea of a self-grading quiz when I saw a link to Flubaroo, a Script App that was developed to be used with Google Forms spreadsheets. However, when using Flubaroo, you first had to receive all of the responses before you could grade the quiz. I wanted to be able to automatically grade a response as soon as it was submitted. Here’s how I did it:
1. Create a quiz using Google Forms and set the response location as a new spreadsheet.
2. Open the responses spreadsheet. Select the “+” at the bottom to add two more sheets. Click the arrow next to the sheet name to rename the sheets. In this example I used “Form Responses,” “Answers,” and “Grades.”
3. Select the “Answers” sheet tab to open it. Click on the first cell in the spreadsheet (A1). Type the “=” (equals) sign. Then click on the “Form Responses” sheet.
4. Click on the column header labeled “A”. Then click on the “Answers” sheet.
6. Select cell B1. Type the “=” sign. Click on the “Form Responses” sheet and click on the column header labeled “B”. Click the “Answers” sheet and select “Enter” on your keyboard. The formula in each cell of column B will now read “=‘Form Responses’!B:B”
7. Repeat this process on the “Answers” sheet until each column in the “Answers” sheet matches each column in the “Form Responses” sheet. To make your spreadsheet easier to read, select the “Text wrap” icon below the spreadsheet menu.
NOTE: This process seems redundant, but it is necessary, since responses will be continually added to the “Form Responses” sheet. Trust me on this.
8. The last sheet will be your grades, so I have surprisingly labeled that sheet “Grades.” Select the first cell (A1) again and repeat the steps to copy column A from the “Answers” sheet onto column A in the “Grades” sheet. Repeat for each column that DOES NOT contain an actual quiz question.
9. When you get to the cell in row 1 on the “Grades” sheet that contains the first quiz question, select the cell, type the “=” sign, and click on the “Answers” sheet. Do not select the entire column. Click on the corresponding cell only.
10. Click on the “Grades” sheet and select “Enter” on your keyboard. Repeat this process for each question on the quiz.
11. After you have finished this process for all questions on the quiz, select the next cell in Row 1 on the “Grades” sheet and type “Grade (Percent)”
12. Now you will want to set the criteria for grading. On the “Grades” sheet, select the cell in Row 2 that is directly under the first question in Row 1. You will want this cell to refer to the cells on the “Answer” sheet, so the formula will contain the following notation:
And the formula will look similar to this:
=if(‘Answer’!D2=“b. Occupational Exposure”,100,0)
For your own quiz, replace everything inside the quotation marks (in this case replace b. Occupational Exposure) with your own quiz answer. When entering this formula, the answer you expect should EXACTLY match the answer on the Google Form. This formula is case sensitive and if there is a space or special character on the form itself, then it needs to be typed the same way in this formula. Note in this example, there is a space between “b.” and “Occupational Exposure”. Select “Enter” on your keyboard. You should see a “0” in the cell.
13. Copy the formula down the column through as many cells as expected responses. Since I have 70 students, I copied down 90 rows, just in case some needed to retake the quiz.
14. Continue entering the answer formula for each question on the “Grades” sheet. Be sure to copy the formula down in each column.
15. Under the “Grade (Percent)” cell, enter a formula to calculate the average of all questions on the quiz. In my example, the first question started in column D and the last question was in column Q. The formula I entered is “=average(d2:q2)”.
16. Copy this formula down the column through as many cells as expected responses.
17. Select the column containing the Grade (Percent). On the top menu, select “Format→Conditional formatting…
18. Click in the blue box and select “is between”. In the first box enter “1” and in the second box, enter the minimum percentage required to pass the quiz. In my example, I required my students to score at least 75% on the quiz. Check the “background” box, and select the color you want the cell to be if students score below the minimum percentage. If you have a lot of students who will be responding, this is an easy way to see at a glance if anyone has failed the quiz.
20. To reduce the number of decimal places in the grade, choose “123” located just below the top menu, and select “Rounded.”
That’s it! Now you can send the live form link to your staff and start gathering responses and calculating grades. Add the AutoCrat Script App to your spreadsheet if you want to let your students know immediately how they scored on the quiz. You might want to set your Google Forms Notification Rule to notify you when a form is submitted, but you will no longer have to spend any time grading the quiz.
Here is the link to a copy of the quiz that I used as my example. It is a “view only” version, but if you open the link and select File→Make a copy… then you will be able to edit the copy you created. You can then experiment with the responses to see how a form submission looks in the response spreadsheets.
If you have other variations of self-grading quizzes, feel free to share them in the comments section.