Google Drive–How to Make a Quiz that Will Grade Itself

I talked about making quizzes using Google Docs in my last post. The quizzes have a wide variety of uses, but this time I am going to tell you how to create a quiz that will grade itself.

One caveat, and it is a large one, is that for this quiz to grade itself each question must have only one answer. I’ll tell you how to work around this later, but it is a little tricky.

Anyway, for now, as I said, you should be sure that each question has only one answer.

Forgive me for going off the topic here, but as I said before, I do not recommend using this type of quiz frequently or even commonly. I also think that, in general, that such a test does not indicate a students real skill level. Having said that, let’s continue.

First, make a quiz. Be sure that there is only one correct answer for each question. If you are not sure how to make a quiz, see my earlier post. Let’s use a four question quiz, taking my Harry Potter example from the earlier post. These will be our questions:

hpquiz

After you have finished making the quiz, go ahead and publish it.

Next, you should take the quiz yourself, and put in the correct answers.

This will give you a key, which you must have later on for this to work.  You need to be sure that these answers are correct.  Double-check for spelling mistakes and so on.
Next, go back to Google Drive main page where it lists your files

You’ll see the spreadsheet for your quiz there. Open it. Of course, your correct answers will appear on the top line.

hpquiz2

 

(Note: If you have made a mistake on your Google Docs form, or if you feel you need to change the quiz questions, you can edit the form from the spreadsheet by clicking on “Form” then choosing “Edit form.”)

Now, let’s grade the quiz. First, we need to make two new columns. These will be “Number Right” (how many correct answers) and percentage (% correct). These should go to the right of the ones that are already there because new answers to your quiz will be filled in under your answers. Like this:

hpquiz3

Now, the tricky part–the formulas.

What you are going to do is to put a formula in the “Number Correct” field.  This formula will count each question as 1 if it agrees with your correct answer. If it does not agree, it will calculate the value as zero.

That means if the person taking the quiz wrote that Harry Potter’s mother was Leala, not Lily, they would get a zero for that question.
Here is our basic formula and what each part means.

This is to grade one answer. In this case, it is the answer in cell C3, here is an example:

=IF(C3=$C$2,1,0)

This means that if the value of C3 is the same as C2 put a one in this cell (the cell where the formula is), and if C3 is not the same as C2, put a zero in this cell.

(There are NO spaces in the formula.)

Here is a break down:

= This must be before every formula in a spreadsheet.
IF This sets up a conditional formula
C3 This is the cell where the answer is that you will grade.
= Do not worry about this second equal sign, since it is part of a condition.
C2 This is the cell that has the correct answer.
$ The dollar signs mean that this cell’s location does not change if you fill down (copy the formulas down the whole column)
1,0 This means that if C3 and C2 are the same, the value is one. If they are not, the value is zero.

Make this for your quiz. For me, I want the answer for B3 to be the same as B2. If it is, the test taker will get one point. If not, they get zero points.

hpquiz4

If you have got that, let’s move on.

I want to add up all the correct answers, giving one point for each that is correct. Therefore, I need to connect the formulas. To do this, we simply add them together by putting a + sign in between the formulas. Keeping with our former example, it should look like this:

=IF(C3=$C$2,1,0)+IF(D3=$D$2,1,0)+IF(E3=$E$2,1,0)+IF(F3=$F$2,1,0)+IF(G3=$G$2,1,0)+IF(H3=$H$2,1,0)+IF(I3=$I$2,1,0)+IF(J3=$J$2,1,0)

Looks like hieroglyphs, but the spread sheet will decipher it for you, no worries.
Of course, the letters and numbers will change for your cells, but feel free to copy and paste this, then change the cell letters and numbers.
You can use copy and paste here to speed things up and make fewer mistakes.  In other words, once you have the formula for the first cell, type a plus +, then you can copy the first formula and paste it. Change the letters and numbers.

Then do it again and again until you have made the whole formula that you need.

Here is what my formula from the Harry Potter file looks like.  This formula goes in cell F3.

=IF(B3=$B$2,1,0)+IF(C3=$C$2,1,0)+IF(D3=$D$2,1,0)+IF(E3=$E$2,1,0)

 

hpquiz5

Next, you need to double-check your formula will work. This is easy. Go back to your quiz (from the spreadsheet, choose “Form” and then “Go to live form” and do it, making a few mistakes as you do so.  Your results will then be in your spreadsheet.  Take a look:

hpquiz6

If you want to then use the same formula in all the cells in column F, you can fill down.  To do so, click on F, and you will see a little square in the bottom right corner.  Then, keep holding the button drag it down, and the formula will paste itself in all the cells below it, automatically changing for each  cell.

filldownresult

 

 

This way, the answers of other people who take the quiz will also have the same formula applied to it.

The next step is to make things a bit more practical.

Let’s make a percentage from the number of correct answers.  This is easy enough if we assume that each answer is worth one point.

For any test, to make a percentage, just divide the number of points the student got right by the total points on the quiz.  Here, we would divide the number that the student got right by 4 (since there are only four questions).

Our formula for cell G3 will be =F3/4

However, your one problem is that this gives you a decimal number, so you need to multiply this by 100 to get a normal number.  Your formula would then be this:

=(F3/4)*100

This tells the computer to divide F3 by 4 first, then multiply it by 100.  That way, you end up with a percentage.  Let’s try it.  Put the formula in and it will do its work.

percenthp

You can then fill down, and that is it.  You have a quiz that will grade itself.  Pretty simple.

Final caveat.  After setting up the quiz form, the spreadsheet, and the formulas, one has to wonder if you are really saving much time over a similar paper test.  Paper tests of this type are easy to grade, so I have my doubts.

However, your students may enjoy this type of quiz once in a great while as a change of pace.  Variation is always good in learning.

Good luck with this.  If you have any questions, write me a comment.