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:

Embedded Video

You can imbed video clips into your website.

For example, a youtube video:



(BTW, youtube is blocked on HIDOE networks.  You can download a video at home at keepvid.com and show it from your hard drive during your lesson to get around this.

Another example:



Here's how I do it:

Voicethread

Do you use powerpoint presentations in your classroom? Upgrade to voicethread for free.  Visit voicethread.com to sign up.

Here is a voicethread on how to use voicethread.  You can click here to watch the presentation in full screen or watch it in the embedded window below: