Teaching with Technology

Whilst messing around today I suddenly thought to myself

“What if you could use the forms in Google docs to set up a spelling test that would mark itself?”

WHY?

• To make life much easier for the teacher and save time marking repeated spelling logs.
• To show any patterns in spelling mistakes.
• To give the teacher more time to teach.
• To allow children at home / absent to take the tests.
• To possibly engage with parents to supervise tests at home and let the child take the test when they are ready and not use school time?

There seemed to be plenty of reasons why I should spend a little time trying it out and to be honest it wasn’t as hard as I thought it would be.   The setting up of the formulae was a little tricky and involved a bit of trial and error, but I got there in the end.

HOW:

1. Open a new spreadsheet and save it.
2. Open three sheets at the bottom.
3. Click on the Share tab and choose fill out a form
4. Write ‘Name’ as the first question then Question 1 etc.   This will put the children’s answers into the first page of the spreadsheet with their names.
5. Rename this this ‘Answers Given’ at the bottom.
6. Create two more sheets.
7. Rename the sheets ‘Results’ and ‘Test Answers’ 
8. NOW THE TRICKY BIT! REMEMBER YOU MIGHT HAVE TO CHANGE THE CELL REFERNCES
9. On the ‘test answers’ sheet write the spelling answers one per cell HORIZONTALLY across.   Then select and drag to fill down the sheet for 30 children or more if needed.
10. On the ‘Results’ sheet in the first column use a fourmula like =’Answers Given’!C1 to copy the NAMES column from your ‘Answers Given’ sheet.  If the name shows up drag the cell down to copy the formula.
11. In the next column put in a formula like this =IF(’Answers Given’!D2=’Test Answers’!A1, “1″, “0″)   This just means if the answer in ‘Answer given’ matches the answer in ‘test answers’ then score it 1 if not 0!
12. Copy across for all the answers.
13. Finally in the last column of the ‘Results’ sheet put Total and put in a formula like =COUNTIF(B2:K2,”1″) This just says add up the previous row if there is a 1 scored
14. SELF MARKING SPELLINGS DONE!

I know that this looks horrific to do, but trust me I’m not very good at these things so if I can do it everybody can.   I’ve trialled it with my class and it really does work!

LESSONS LEARNT

• Google docs can be used in creative ways to help reduce workload.
• Doing this for spellings will allow the teacher to spend much more time on teaching not marking.
• You could also use the embe
• The possibilities of sending out linked exercises etc with the email to support the learning is an exciting possibility.

I’m also looking into other areas where this sort of collated marking could be useful and will post as soom as I have thought it further through.   If you want to see it in action then leave your email and I’ll invite you to have a look.