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!
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.
- 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.
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)"
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.
- Create a short self-grading assessment test.
- Post a link to it so we can take it.