Result – Self Marking Spellings in Google Docs.
Posted by: mrkp in Google Docs, tags: Google Docs, homework, spellingsWhilst 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:
- Open a new spreadsheet and save it.
- Open three sheets at the bottom.
- Click on the Share tab and choose fill out a form
- 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.
- Rename this this ‘Answers Given’ at the bottom.
- Create two more sheets.
- Rename the sheets ‘Results’ and ‘Test Answers’
- NOW THE TRICKY BIT! REMEMBER YOU MIGHT HAVE TO CHANGE THE CELL REFERNCES
- 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.
- 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.
- 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!
- Copy across for all the answers.
- 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
- 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.
Entries (RSS)
June 10th, 2008 at 10:13 pm
Hello Steve, I have finally found your blogging space through your tweet. Thanks for sharing this one. I really like working with google docs and this just adds another dimension to its powerful ability. Do you ever work with tombarrett as he does a lot of work with google docs as well?
Another great feature that I have used for collaboration and ease is that of the google forms.
June 10th, 2008 at 10:43 pm
Nice one. I Haven’t played with google docs too much but I used to do this in Excel and I guess the two are getting pretty similar these days.
One variation for younger kids is to use the Wingdings font for the marking column and display a smiley face if they get it right and sad face if wrong ( I think J and K) then do a countif on the J’s to see how many correct
June 11th, 2008 at 2:14 am
[...] Channel-V wrote an interesting post today onHere’s a quick excerptWhilst 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 … [...]
August 23rd, 2008 at 9:40 pm
[...] if they are correct or not and it becomes self marking. Steve Kirkpatrick had this brainwave a while back so check out his excellent post for more information about setting up the spreadsheet. Danny also [...]
[WORDPRESS HASHCASH] The comment’s server IP (72.34.53.165) doesn’t match the comment’s URL host IP () and so is spam.
August 25th, 2008 at 11:40 pm
I have just found your using gdocs to grade spelling tests.
I love it.
I have been playing around and have added some things you may think is interesting.
I have it put a 4 for a correct answer to go along with my point system. I also have it put a 1 for Bonus words to go along with my system.
If a 4 or 1 does not show up then I have the sheet show the word the way the student typed it.
I also have a formula to count how many times a word was spelled correctly.
Just some ideas…
See here…
http://spreadsheets.google.com/pub?key=p5M3ruvf9-maSSwwQsUWoaw
August 26th, 2008 at 8:53 am
Thanks Kevin, some great additions. The thing I love about the internet is the way that people can always improve on you idea in ways that you couldn’t think of yourself!
August 27th, 2008 at 8:48 pm
[...] next project is to fully implement the self-marking spelling idea that I’ve posted about before. I’ve had to set up a different file for each [...]
[WORDPRESS HASHCASH] The comment’s server IP (72.34.53.165) doesn’t match the comment’s URL host IP () and so is spam.
September 20th, 2008 at 5:55 pm
I have taken this to a whole new level.
http://spreadsheets.google.com/pub?key=p5M3ruvf9-mZ1RNpwELyeNA&gid=14
I have a list that is easy to input and that “goes to” the key.
I now have the students give a “secret” word and they use that to immediatly see their results.
I have each word each word evaluated and graphed with a tug O War.
I have color coded so I can immediately see A’s and over 100’s!
It is GREAT! The best thing I have done in my room in a long time!!!!
Thanks!
September 22nd, 2008 at 2:29 am
Check this out….
http://spreadsheets.google.com/pub?key=p5M3ruvf9-mYO3sFw1kJPWA
I have really enjoyed playing around with this.
* Spelling list on the first page
* Tug O War Chart
* Class Average gauge
* Student Secret Word lets them know their score and words missed right away!
* Grade book sorted by Homeroom for me
* Color Coded A’s and A+
This is the hilite of my year so far!
Thanks!
February 15th, 2009 at 8:47 am
A great idea and one I’ve started to try. Any idea if this would work with individualised spelling tests? I want students to create their own lists and an audio file of each word. From there, they can click on the audio file for each word.
Cheers,
Mark
October 29th, 2009 at 2:21 pm
[...] if they are correct or not and it becomes self marking. Steve Kirkpatrick had this brainwave a while back so check out his excellent post for more information about setting up the spreadsheet. Danny also [...]