Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Normalize score
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Normalize score
I'd probably use some kind of Z-score method.
"austuni" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Normalize score
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. "austuni" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Normalize | Excel Worksheet Functions | |||
score | Excel Discussion (Misc queries) | |||
Normalize frequency | Excel Discussion (Misc queries) | |||
Q: Using lookups to normalize a database? | Excel Discussion (Misc queries) | |||
Normalize the second pie in a pie of pie chart | Charts and Charting in Excel |