Skip to main content

Auto-Grading an Office365 Excel Survey Assessment

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(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

Kyra Andreson said…
This comment has been removed by a blog administrator.

Popular posts from this blog

So you want to hack your OneNote Class Notebook

Taking a brief break from my "Getting Started with OneNote Class Notebook" series (you can start that one here )... This is a little advanced so if you're not comfortable setting permissions inside of Office365 you may want to avoid this.  Or set up a Class Notebook to play with so that it doesn't affect any existing Class Notebooks.  Yeah, the latter is a good option. One of the great powers of OneNote is that you can do some really neat permissioning of the Section Tabs. When the Notebook is created, of course, it gives you an "open permissions" on the Collaboration Space and student-read-only on the Content Library.  And then each student space is wide open to each individual student. But we've found that occasionally you want to mix up the permissions a little.  For example, you could create a space in a student section for your private notes that the student couldn't see, or maybe you want a tab in the Collaboration Space that students cou

Desmos, OneNote & Replay

So using Desmos activities are a great way to encourage exploration and discussion in math class -- if you haven't tried them, I encourage it.  They're collected at  https://teacher.desmos.com/  But ... Desmos doesn't give you quite enough.  It doesn't have a way of capturing the work that the student does within their space, and it doesn't allow for annotation of class contributions as we come together to discuss.  Well, not surprisingly, OneNote comes to the rescue.  Using the Windows shortcut Windows-Shift-S it is really quick to snag the Desmos screen and pop it into a waiting OneNote page.  From there, we can grab our pen and (using wireless projection) talk about what all the different responses mean and where to go from there. (An aside : one of the nice features of Desmos activities are the way you can hit PAUSE and it will pause all the screens of the students working.  I always give them a heads up "10 seconds to pause..." and it's refr

Equation Editor comes to OneNote (Win10)

Folks have been waiting a while to get Equation Editor into OneNote (Win10 version... it's been in 2016/Desktop for forever). Now, the funny thing is this won't make a huge deal for me. I tend to just write my equations out, and if it's for more serious distribution I tend to write it in Word.  But for others, this may improve the way they work in OneNote.  And I also think I'm not allowed to call it Equation Editor, but I'm going to ignore that. Make sure you've updated your OneNote (go to the Store and check for any Downloads & Updates). I recommend folks visit the Store regularly to get any updates. I'm never sure how often it looks for updates on its own and Microsoft has moved to a continual, if gradual update process for all of its apps. To start entering equations, click on the INSERT ribbon and then on EQUATION.  You may think, "why not just click on the Math button?" but that is to translate digital ink or text writing into a mat