Online Test - Automatic Grading :)

Spreadsheets are a beautiful thing.  For us computer nerds.

For everyone else, they can be scary.  I will show you why you need to stop worrying and love the spreadsheet.

Take this test, and please hit "Submit" when you finish:




Good job!  Now check this out:

Here are the test results.  Use the formula under the "score" column to grade your OWN tests.

Here is that formula again:

=if(B3="","",(100*ARRAYFORMULA(SUM(C$2:E$2=C3:E3))/3))


Here is what the different parts DO:

=if(B3="","",(100*ARRAYFORMULA(SUM(C$2:E$2=C3:E3))/3))


The SUM function adds up all of the answers that are equal to those in row 2, where I put my response to the form. The ARRAYFORMULA function tells the computer to match complicated text answers, as opposed to simple numbers. This is limiting in that it cannot grade open-ended essays or short answers. I answered the quiz before any students, and they can't see the answers, so I tell the computer that the correct answers in row 2. You need to do the same.

I divide by three (/3) because there are 3 questions, and I multiply by 100(100*) to get a percentage value.  I use the nested "if" function to tell the spreadsheet NOT to grade a row if it is blank ("").  This part is not strictly necessary, but it makes things easier for me.  The if function works like this: =if(something is true, do this, if false do this), so my if function checks to see if the answer is blank, if it is then it leaves the score blank, if it is NOT blank then it grades test and enters a score.

And, of course, a voicethread to show you how to assemble your test and grade it: