For those who teach, testing is an integral part of what we do.  We write test and homework, grade, evaluate, repeat.  But at the end of the day, it is we as teachers who need to learn for/from the test.

One of the best ways to determine if a question on a test is a good one is to evaluate how many got it right or wrong, and who got it right or wrong.  Two numbers are helpful in understanding the quality of a question: difficulty and discrimination.  Difficulty is easy to arrive at: it’s simply the number of people who got it right, over the number of questions.  If 9 out of ten got an item right, the difficulty index is .9.  That number tells you whether a question is easy or difficult.

The second number, discrimination, is considerably more complicated to calculate, but also very valuable.  Discrimination tells you if the question relates to performance on the rest of the test.  In other words, did people who did well on the question do well on the test- did people who knew the material get this question right.  You might be surprised what this number can tell you about a question.

In my Measurements of Musical Behavior Seminar with Dr Patti Sink we used something called the point-biserial correlation to determine discrimination.  Calculating the discrimination this way is a big mess because it is conditional math.  That is, some numbers get added if something else happens, but not otherwise.  It’s easy to do by hand for small numbers of students and questions, but gets very hairy very quickly as the class or test grows.  That is, unless you take advantage of the power of spreadsheets and macros.

Since I was a professional programmer for a while, I decided to help out my classmates and my community by writing a VBA macro to do the messy stuff automatically. I wrote a function called Discrim() that takes two ranges: one of item scores, and one of test scores to produce a discrimination value.

I present to you, the Discrimination Function for Excel, with instructions on loading it into your own spreadsheets.

This class has been a lot of fun, but also a great learning experience.  I hope to use this knowledge to write better test, but also to improve my teaching, and helping those around me to understand the power of testing.  Comments and feature requests are welcome!