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.
