I could be very late to the party finding this out. If so, excuse my excitement.
I gave a multiple choice test for my IB Physics course last week. Since I am using standards based grading (SBG), I wanted a quick way to see how students did on each standard. I made a manually coded spreadsheet eight years or so ago to do this. It involved multiple columns comparing answers, multiple logical expressions, and then a final column that could be tallied for one standard. Multiply that by the total number of standards...you get the drill.
I was about to start piecing together an updated one together using that same exhausting methodology when I asked myself that same question that always gets me going: is there a better way?
Of course there is. There pretty much always is, folks.
For those of you that don't know, the SUMPRODUCT command in Excel does exactly what I was looking for here. It allows you to add together quantities in one range that match a set of criteria in another. Check out the example below:
The column labeled 'Response Code' contains the formula '=1*(B6=E6)', which tests to see if the answer is correct. I wanted to add together the cells in F6 to F25 that were correct (Response Code = 1) and had the same standard as the cell in H6. The command in cell I6 is '=SUMPRODUCT((F6:F25)*(E6:E25=H6))'. This command is equivalent to the sum F6*(E6=H6) + F7*(E7=H6)+F8*(E8=H6)+...and so on.
If I had known about this before, I would've been doing this in some way for all of my classes in some way since moving to standards based grading. I've evaluated students for SBG after unit exams in the past by looking at a student's paper, and then one-by-one looking at questions related to each standard and evaluating them. The problem has been in communicating my rationale to students.
This doesn't solve the problem for the really great problems that are combinations of different standards, but for students that need a bit more to go on, I think this is a nice tool that (now) doesn't require much clerical work on my part. I gave a print out of this page (with column F hidden) to each student today.
Here is a sample spreadsheet with the formulas all built in so you can see how it works. Let me know what you think.
Exam Results Calculator