Quick and Easy IF Formulas for Grading Google Forms

I recently led a Google Docs session at Whittier Christian High School and was asked to provide a quick demo video illustrating the way I used formulas (and in particular the IF formula) to grade a quiz made using a Google Form. Apparently my explanation plays well face-to-face but considerably less well from memory. ;)

For this video I cut right to the chase and did my best to explain the IF formula for beginners… while keeping under a 5 minute time limit. I hope it’s helpful for you and your colleagues and I hope you’ll let me know what you think.

Incidentally, there was a minor error in one of my formulas at the end, but I fixed it live rather than record (yet another) take. I like to think it’s good time management, and good modeling of verifying and troubleshooting a spreadsheet formula. Oh, and I wouldn’t necessarily “reteach” if half my class failed a quiz and the other half got As… but that might be a good time for some targeted intervention. ;)

Also, here are the slides I used in the middle segment of the video… in case they might be helpful to you as well (the last slide includes a copy-and-paste-able “nested” IF formula for generating letter grades from percentage scores):

Up next in the screencast category: Using Transpose to share individual results of a teacher observation (by an admin) or student rubric (by a teacher or peer), complete with a demo template. :)

7 Responses to “Quick and Easy IF Formulas for Grading Google Forms”

  1. ktenkely Says:

    This is SO helpful, I didn’t realize that Google Forms allowed this kind of formula and function. Thanks Mark!

  2. Chris Sanita Says:

    Thank you for doing this. Several of my teachers are going to use this. I will invite you to their examples.

  3. Lisa Thumann Says:

    Thanks for sharing this Mark. Sheila Beck, Sean Beaverson and I also worked last year to write up some written directions. I thought you might want the link to the public Google Doc:

    http://docs.google.com/View?id=ddn2z86w_1047f6jkfkfc

  4. Mark Wagner, Ph.D. Says:

    Here’s an interesting follow up question from my email (and my response), in reverse chronological order of course.

    Hi, Courtney. As far as I know there is still no way to embed images into a Google Docs form. However, if you’re comfortable editing HTML and uploading a file to your own webspace somewhere, you can more or less copy the HTML of a form, add your images (without breaking any of the other functionality) and upload it to your own site – and point students there to take the quiz. Here are directions if you’re interested: http://www.google.com/support/forum/p/Google%20Docs/thread?tid=19c0642040365774&hl=en

    -Mark

    On Mon, Oct 4, 2010 at 11:14 AM, Courtney Rudd wrote:
    Hi Dr. Wagner

    I am a high school teacher that utilizes Google Docs. I read about your self-grading quizzes post (reposted on Freetech4teachers.com) and realized you might know how to solve this Google forms problem:

    I would like to add images to my quizzes as part of the question.
    For example, I would like to place 4 images in a question to ask students, “Which of the above images represents the carbon cycle” or something similar
    I have searched for this solution and it doesn’t appear feasible as of now if I do not have a website to edit the HTMLcode to ‘fool’ the images into the form’s code.
    Any tricks up your sleeve???

    Thanks, Courtney Rudd
    Renaissance High
    La Selva, CA

  5. EdVentures in Technology » Diigo Links 10/30/2010 Says:

    [...] Quick and Easy IF Formulas for Grading Google Forms [...]

  6. Using Google Forms to Create Self-Checking Quizzes | Learning Technology Together Says:

    [...] I would also encourage you, if you haven’t already, to subscribe in Google Reader to the RSS feed for Tammy’s tech tip blog and skim past posts for some great tips (and possible topics for you future post in our class blog). Finally, you might want to check out other templates of Tammy’s in Google’s template gallery – she has some great ones from collecting information from students or parents to rubrics, mad libs, and book reviews. For a refresher on templates in general, check Karin’s earlier post on Using Templates in Google Docs where she described the Lynda.com tutorial/video about Google’s public template gallery. If you are interested in learning more about how the IF function works in spreadsheets, check out Quick and Easy IF Formulas for Grading Google Forms. [...]

  7. Using Google Forms to Create Self-Checking Quizzes | Teachers Learning Technology Together Says:

    [...] That’s where the fun comes in with Tammy’s templates and what makes them so much more cool. They automatically calculate the percent of correct answers for each student. That’s why they are called “self check” – because all you have to do is enter the correct answers in the designated row of the “answers” spreadsheet where the student responses already are and then switch to a second sheet named “scores” where Tammy has entered formulas to compare each student’s answers to your correct ones (using the IF function) and  formulas (using the sum function and division) to calculate and display the percentage correct for each student. How nifty is that! While you don’t actually have to understand how the formulas with functions work, if you want to see what’s happening behind the scenes in Tammy’s templates, check out Jennifer C.’s post, Excel 2007 SUM and AVERAGE Functions and other videos about doing calculations in either Excel or Google Spreadsheets. If you are interested in learning more about how the IF function works in spreadsheets, check out Quick and Easy IF Formulas for Grading Google Forms. [...]