In a previous post, I wrote about creating a simple e-learning module with Google Forms. Then I discussed sending students an immediate response when they completed that module, which is very helpful for record-keeping and for worried students who believe their responses are floating somewhere in cyberspace. To continue this line of thinking, what happens if students are required to demonstrate a certain level of competency in order to get credit for the learning module? Is there a way to automatically grade student responses and give them different feedback depending on whether or not they pass? Well of course there is! Keep reading ...

Step One: Create your quiz in Google Forms

This works best with multiple choice items, but you could try to use fill-in-the-blank or constructed response items. Spreadsheets will look for identical matches, so the students' answers would have to match your answer exactly. I suggest multiple choice.

Step Two: Take the quiz and copy responses to adjacent columns

You will need to have a copy of the correct answers to use as an answer key, and the best way to do this is to take the quiz yourself. This is also a great way to catch typos, poorly constructed phrases, and other mistakes that might confuse students. Once you have taken the quiz, click Submit and copy and paste your correct answers into adjacent columns next to the responses, as illustrated below:

Step Three: Use the IF function to check student responses against answer key

You now use the correct answers in the adjacent columns to check all of the incoming responses when people complete your form. You will use the IF function:

=IF(F2=$AH$1,1,0)

If your quiz has a lot of questions like mine does, this can be a little tedious. Essentially, you are telling the spreadsheet to check cell F2 against AH1, and if they match give it a 1; otherwise, give it a 0. You will need to make sure you include the $ before the row and column address of the correct answer. This will keep the cell constant as new responses come in. If you don't do this, the spreadsheet will automatically add 1 and check the new response in F3 against AH2. Just like in life, $ can be the great stabilizer (except when it's not).

Step Four: Create a cut-off score and indicate if a quiz is passing or not

You will need to create a new worksheet called "Rank" and indicate the cut-off score for each quiz. In my case, it is 80%, so the array looks like this:

After this, you use the VLOOKUP function to compare scores to the array. Any score between 0-80% will get a No, and every score 80% and above will get a Yes. Of course, you will need to use a few other functions to calculate the score. They are listed below:

Use COUNTIF to count the correct scores: =countif(AH2:BI2,"1")
Use SUM to convert the raw score to a percentage: =sum(BJ2)/28
Use VLOOKUP to assign a rank based on the score: =vlookup(BK2,Rank!$A$1:$B$2,2,TRUE)

All the VLOOKUP function is doing is comparing the value in BK2 to the array in the Rank worksheet. The 2 means it uses the values in column two as the output, and TRUE means it finds the closest match rather than an exact match.

Step Five: Use CopyDown add-on to automatically score quiz

The CopyDown add-on will do a lot of the work for you at this point. You will simply direct CopyDown to apply the functions in row 2 of your spreadsheet that you want applied to subsequent submissions to your form, and it will perform the calculations automatically.

Step Five: Use FormMule to send differentiated feedback based on score

Now that you have a spreadsheet that automatically scores each new submission and tells you whether or not it met the cut-off, you can assign different e-mail messages for students who Pass or Did Not Pass. The first thing to do is tell FormMule you will have 2 templates:

Send Condition

You will then create templates for each send condition. For my quiz, I send students who pass a certificate, and students who do not pass get a message with the bad news. You can see the templates below (Warning: they contain HTML):

PASS

DID NOT PASS

I would suggest you test this a few times to make sure it works correctly. I will warn you, this will make you giddy and you may start doing it just for fun. You may even start looking for excuses to make self-grading quizzes with different templates. Give it a try and be amazed at this little piece of Google magic.

Blog

Sending differentiated responses to students with a self-grading quiz