So it’s pretty common that folks use Google Forms to create an automatically graded assessment. Now, I have to admit, as a mathematics teacher I don’t do a lot of fixed-response assessments like this that need automatic grading. But that doesn’t mean it isn’t a useful option.
In fact this post arises not from automatic grading but rather a situation in our Psychology course. The teachers were doing a study with their students and wanted to collect the data easily and run a T-Test with as little work as possible. (A t-test, roughly speaking, determines whether two groups are different from each other.)
We set up an Excel Survey for the students to enter their results and then in a second sheet in the spreadsheet containing the Survey, we laid out the T-Test. As the results came in, the T-Test continually re-evaluated. Success!
I figured if it can work for a T-Test, it can work for a simple multiple-choice, True/False or simple word response. So I head over to my OneDrive and create a new Excel Survey.
Excel Survey is a little limited… you get Text, Multiple Choice, Yes/No, Date and Paragraph. Now, I’m not going to assess Paragraph because that’s a little more complicated evaluation. But the rest of them are pretty easy to put into an IF algorithm.
You’re welcome to enter your own responses here: Link to Survey
On the next sheet, I set up the Grading algorithms
Each of the Grading algorithms is rather simple:
If the second column ($B2) in the Survey1 worksheet (and the Excel Survey always calls the results spreadsheet Survey1) is equal to “Answer” then put a score of 1 in the cell otherwsie put a 0.
I then add up all the scores to get the Raw Score and then calculate a percentage.
 |
| This is Sheet1 – the survey results from Excel Survey |
In case you didn’t know, to reference a cell in Excel that’s on another worksheet, the reference is WorksheetName!CellName … the exclamation point separates the worksheet name and the cell you want.
And then we copy the formulas down the sheet so they calculate as the responses come in.
 |
| And this is the Grading Sheet (I renamed the sheet Grading Sheet) |
