An assessment test with Google Docs

This is another task for the ELTAS Tech Tools Day.

Pre-task thinking:

Let's say you want to send clients an assessment test for them to do from home, and all the data should be evaluated instantly, without you having to correct it. You would like to have all of the data neatly in one table, easily available. This could be for a general assessement test that you use again and again.

I've got a very short test on Making appointments on the phone just to show what the front end of such a self-grading test could look like to the client:



When you do it, you'll notice:

• you get no feedback!
  

That's because all of the answers go into the spreadsheet and I have not included a correct/incorrect message. This may be a bit weird for the assessment test takers.
It's fantastic for you, though. Have a look behind the scenes at the spreadsheet, and you'll see:

• the answers are neatly entered in a spreadsheet with a formula that evaluates the answer and gives points accordingly, which are added up.
• Note that new data that has gone in needs to be formatted simply dragging the formula in each column down over the new data below.
  

Step by step:

• If you haven't already, please study the Google Docs Form/ Spreadsheet entry on this blog first.
• Then watch this video explaining the self-grading feature.
• Finally come back and study the sample spreadsheet and its formula.

How the formula works:

The trick is to set up your quiz and enter the correct answers yourself through the form. Then go into the spreadsheet itself and add those answers as headers, or questions, at the top of new columns. Follow up by entering a simple formula in each of the columns below the answers to allow the correct answers you enter in the spreadsheet to be checked against the answers given by the test takers.

This is the formula in words:
If the student's answer is equal to your answer, then he/she gets X points; otherwise he/she gets 0 points

In our case, I've decided to give each correct answer X=20 points, adding up to a total of 100 for the 5 questions. Look at the formula:

• In H2 you'll see "=if(C2=$H$1,20,0)"
• In H3 you'll see "=if(C3=$H$1,20,0)"
• In I2 you'll see "=if(D2=$I$1,20,0)"
• In I3 you'll see "=if(D3=$I$1,20,0)"

...etc.

The $ signs before the correct answer field let you avoid changing that particular letter/ numeral in the formula when you drag the formula down the page.

Task:

• Create a short self-grading assessment test.
• Post a link to it so we can take it.

Dear teachers, this may look daunting at first, but never fear, try it out and you'll see it's doable and soon it'll be a snap. Any and all questions welcome!