I have already outlined how to grade a quiz using the Google Drive add-in called Flubaroo . Well, in this post I will share with you how to grade a multiple choice quiz in Google Drive without a plugin. Let’s get cracking; it is very easy to do.
Step 1: Create Your Quiz
You need to create your quiz using the Google Drive forms as we outlined in an earlier post.
Step 2: Select Results Destination
Select your destination for the results. I almost always create a new spreadsheet for my results. Once you have created the test, you will then go ahead and take it yourself to be the first person to have completed the test, and to create the answer sheet. Click here to see the answer sheet in action.
Step 4: Create Results Columns in the Spreadsheet
You can do this step before the results come in to make it a lot easier for yourself. I will be using the test or quiz above for this example. The first thing that we will do is go to the spreadsheet that is now in your Google Drive. Open this spreadsheet and create a heading for each question. In my example I have 3 questions, so I will create additional headings as follows:
Question 1 — Question 2 — Question 3 –Total Score — Percentage Score
Step 5: Add Cell Formulas
Now you need to add a formula to the column “Question 1”. This formula records the predetermined score for getting a question right or wrong. For this example we will say a right answer is equal to 10 points and a wrong answer will be equal to zero points.
The script will look for the correct answer using a formula, remember you will use your response as the answer sheet. If the response matches this cell then it is TRUE, and 10 points will be assigned. If there is no match a FALSE will be returned and a zero assigned.
Step 6: Continue Adding Formulas to Cells
Create the formula to create “Question 1”: Since I did the test first I will be using my row as the answer sheet. For question 1 I will use
“=IF($E2=$E$2, 10, 0)”
That means that if the response matches the response in column E2, then 10 points will be given. If it does not then a score of 0 will be given. After you have added that formula, you will then drag it downwards to assign that formula to each cell.
Step 7: Keep Adding Formulas to Cells
Create a formula now for ”Question 2”: You will repeat step 7 above for the other question columns; Question 2 and Question 3.
For example, the formula for Question 2 will be =IF($F2=$F$2, 10, 0), and the formula for Question 3 will be =IF($G2=$G$2, 10, 0). If you have more questions, you will repeat this process for each column.
Step 8: Total The Scores for Individual Students
Add the entire total for the users’ or students’ scores by using the formula =SUM(H2:J2). This formula will total the users’ scores for you. Place this formula into the cell to the right of the last question.
Step 9: Calculate the Percentage Scores
I have made the spreadsheet public, therefore, you can click around and see how I have my formulas. The formula I used to calculate the percentage is =K2/30*100. Basically, it’s dividing the total score by 30 points and then multiplying by 100.
Conclusion
You can see that there are a few steps involved here. However, you will get the hang of it and you will be able to collect and analyze your test results in a flash.
Give your feedback below.
Leave a Reply