Normalize score


G

Guest

I work in exam department and there are several people who marks the same
question. In order to be fair on the candidates, I would like to normalize
the scores of all the markers. Can anybody help me with this please?
 
Ad

Advertisements

Ad

Advertisements

G

Guest

Ah, okays. This is what you do:

Place the scores from each examiner in a seperate "block" of scores in the
same column. For example, if there are four markers with ten scores each...
You can place the first block of scores from the first market into A1:A10,
the second marker's score sinto A11:A20.

From there, you type these into the first cell of the second column:
=(A1-AVERAGE($A$1:$A$10))/STDEV($A$1:$A$10) and you drag that formula down
until the last the first marker.

When you get to the first cell in the second column that is adjacent the
first cell in the second marker's block of scores in the first column, you
change the formula slightly: =(A11-AVERAGE($A$11:$A$20))/STDEV($A$11:$A$20)

Repeat as necessary.

When you're done, the entries in column B are now normalised scores. (also
known as standardised scores) The column's scores will have a theoretical
average of 0 and a standard deviation of 1.

If you want to then change the scores back into a scale of 0-100, you'll
need to do this in column C:

(Formula in C1) = Number1+Number2*B11

Where Number1 and Number2 are numbers that you need to choose yourself. I
can't tell you what numbers to use, since it's up to your school's policy.
Normally, you'd create a density plot then adjust the two numbers to get the
shape and behaviour that you like. Look up how to calculate a guassian kernel
density if you wish, or if you really want to, I can send you an MS2004 excel
file that has a macro in it that calculates and plots it for you. Although,
i'm pretty sure there are plenty of those tools available on the internet
anyway.

That's the simplest way of doing it, but it's not the most elegant.

Just beware of the risks of using standardised scores, though. It might be
possible that a class is actually better than another class, and
standardising the scores might actually disadvantage the better class.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top