Convert list of test results into a comparative score

Question

My students have completed a touch typing test that have given them a WPM (words per minute) result.

I now want to give a score to those results out of an arbitrary number, say 10. I will later use this number for weighting with other tests for a final end of semester score.

I also want the touch typing test score to be comparative to the WPM result so that if a WPM result is miles above the next result, the scores may drop from 10 (1st student) to 7 for the next student - with no one in the class getting a score of 8 or 9 - thereby indicating that the student who got a 10 is significantly out-performing the rest of the class.

Sample data:

Student   | WPM Result
----------------------
Student 1 | 54.4
Student 2 | 61
Student 3 | 86
Student 4 | 52.5
Student 5 | 45.8
Student 6 | 117.7
Student 7 | 107.8

I have all the data in Excel, so I can work with a formula type answer.

Any thoughts?

Answer 1

In one of our courses, we use a linear grading scheme between an upper bound for the score and a lower bound of the score. Suppose the upper bound is $b$ and the lower bound of the score is $a<b$. Since you want to give a score from 1 to 10 (10 being the best), you have $\#=10$ different grades. Then the formula to convert WPM scores to grades is $$\text{grade}=1+\left\lfloor\frac{WPM-a}{(b-a)/\#}\right\rfloor,$$ where $\lfloor x \rfloor$ is $x$ rounded down to integer (e.g., $\lfloor 1.3 \rfloor=1$). (This should be easily implementable in Excel.) Thus, if $WPM-a<(a-b)/\#$, then $\left\lfloor\frac{WPM-a}{(a-b)/\#}\right\rfloor=0$ and $\text{grade}=1$. That is, the student did not earn enough points (WPM) above the lower bound $a$ to reach the threshold necessary for the next better grade. Thus, he gets the minimum grade.

For example with $a=30$ and $b=130$ (everyone below $WPM=30$ gets grade 1, everyone above $WPM=130$ gets grade 10) we have $$\text{grade}=1+\left\lfloor\frac{WPM-30}{10}\right\rfloor,$$ so the grade for $WPM=45.8$ is $2$, while the grade for $WPM=86$ is $6$.

You can adapt $a$ and $b$ to make grades better or worse. With $a$ or $b$ smaller grades will be better.