Calculating Percentiles of Test Scores?

[ddawg09]

I have a list of 75 test scores, and I have to calculate the percentile
of each test score and what percentile corresponds to what score.
Anybody have any ideas to make this easier? I already put all the test
scores in order.

 

[Guest]

Normalize the scores to the maximum (not perfect) score:


Say you have 75 scores in column A sorted from highest at the top to lowest.
In B1 enter:

=A1/$A$1 and copy down thru B75. Format column B as percentage.
or
=100*A1/$A$1 and copy down thru B75. Format column B as number fixed places 0


If you had thousands of scores you would bin them. For example, all scores
between 74.5% and 75.5% would be in the 75th percentile.

 

[Guest]

IGNORE MY PREVIOUS POST:


Percentile is related to position in the list rather than relative score.
In B1 use:
=(COUNT(A:A)-ROW())/COUNT(A:A)*100

Percentile should represent the percent of the population with scores lower
than the given score.

 

[hightide]

=(RANK(A2,$A$2:$A$76,-1)-COUNTIF($A$2:$A$76,A2))/COUNTA($A$2:$A$76)
Does not require the list to be sorted

 

[vezerid]

You might also want to look at the PERCENTILE and PERCENTRANK
functions. They do not require sorting of your data.

I really appreciated the other suggestions too, by GS and hightide. To
me they showed a nice way to present the concept in class.

HTH
Kostis Vezerides