Saturday, December 22, 2007

All Hail The Mighty Excel For Grading

As a finance guy, I'm pretty good with Microsoft Excel. MOst of my immediate peers use it for grading. But if you do, here are a few functions that you might find useful:

1) The IF function is useful if you have two different weighting schemes. For example, to give the poorly performing students incentives to push hard in studying for the final, I often tell the students that I will grade them using two different weightings on their grades - in one I put greater weight on the exams and quizzes that take place throughout the cours, and in the other I put more weight on the final (I assign them the higher of the two grades).

Use the syntax =IF(Scheme1>=Scheme2, Scheme1, Scheme2) where Scheme1 and Scheme2 reference the cells containg the scores under the two weighting schemes, and you'll get the higher of the two cells.

2) The LARGE function is very helpful when you want to pick N out of K scores (for example, if you calculate the average of quizzes after trhrowing out the lowest N scores). The Syntax LARGE(A1:J1,2), will identify the 2nd highest score out of the 10 elements soted in the array from A1 to J1. To calculate the average of the 8 largest scores in the cells from A1 to J12, I'd use the following syntax:

=(SUM(A1:J1) - LARGE(A1:J1,10) - LARGE(A1:J1,9)) / 8

In other words, I calculate the sum of all ten, then subtract the two lowest scores,and then divide by eight (make sure you keep track of the parentheses).