Standards Based Grading(SBG) and The SUMPRODUCT Command
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
Bless you Evan!
Thanks for sharing. Could you post ainl to the excel file? Or perhaps you could email it to me
Chck out the bottom of the post for the spreadsheet file.
Evan, with multiple questions aligned to specific standards, how are you determining mastery for the standard?
Hi Bob,
I’m counting the number of questions students have perfectly correct. For students with most questions perfectly correct, I’ll generally mark them as proficient. For students with most incorrect, I’ll probably mark them closer to a 1 or 2. In both cases, I take another look at their exam to make sure the proficiency level I’m giving them is consistent with their level of work on the exam.