As I was cleaning up my Google Drive account I found a spreadsheet/form that I created back in the beginning of the year for teachers at Seymour High School who were using Google forms for assessment. Using a few simple formulas, I was able to have the spreadsheet automatically score student responses but I also added some functionality to have the student's score and comments e-mailed to them. It's pretty cool.
To try out this assessment collector, click here and make yourself a new copy for your Drive account.
After you have saved it to your account your are ready to modify the questions to fit your assessment. If you are familiar with creating Google Forms this will be a piece of cake but if not you can check out this tutorial to get you through it.
Once you have included your questions on the form it's time to input the answers in order to have them corrected automatically. Start by taking the actual quiz yourself...hopefully you get a hundred. In the name field you can put something like "Answer Key" and use your e-mail address.
Note: If you would rather watch a video tutorial (vs reading the text version) on how to modify the form, scroll down.
In order to get the spreadsheet to auto-correct you have to use a formula further down the line to assign a point value for each correct response. So...in the "Sample Quiz" form for cell L2 you should have =IF(E2="answer",1,0) where answer is the correct answer to the question. You can use multiple choice or it could be short answer but whatever you type inside the parentheses will be the only accepted response. You will need to do this for the rest of your questions. Moving along the next formula would be in cell M2 where you would put =IF(F2="answer",1,0). Essentially the formula is saying to check cell F2 and if the correct answer is there put a one in M2. If the correct answer is not present it puts in a zero.
To tally up the score you will need to use the formula =sum(L2:P2) in cell J2.
The next step is to add your comments in to column K. This is where you can offer feedback to the student about how they did and their areas of strength and weakness.
Once you have put in your comments, you click on a cell in the row you want to send then click on "Teacher Menu" and "Send Scores & Comments". That's it...you're done!
To watch the video equivalent of these directions see below:
Now What?
Using a form like this will get you fast, informative data that you can use in your classroom to guide your instruction. The fact that you can add anecdotal feedback is what separates this form from the rest of the pack. I hope that you find it useful and please share it with your colleagues.
