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:
I then add up all the scores to get the Raw Score and then calculate a percentage.
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.
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(Survey1!B2="Answer",1,0)
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) |
I've shared the Office365 Excel spreadsheet in case you'd like to see how things change: Link I've hidden the email column so in case you enter your email it's not publicly visible.
Comments