Calculating Percentiles of Test Scores?
 

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.


--
ddawg09
------------------------------------------------------------------------
ddawg09's Profile: http://www.excelforum.com/member.php...o&userid=31205
View this thread: http://www.excelforum.com/showthread...hreadid=514082

Calculating Percentiles of Test Scores?
 

Normalize the scores to the maximum (not perfect) sco


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.
--
Gary's Student


"ddawg09" wrote:


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.


--
ddawg09
------------------------------------------------------------------------
ddawg09's Profile: http://www.excelforum.com/member.php...o&userid=31205
View this thread: http://www.excelforum.com/showthread...hreadid=514082

Calculating Percentiles of Test Scores?
 

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.

--
Gary''s Student


"Gary''s Student" wrote:

Normalize the scores to the maximum (not perfect) sco


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.
--
Gary's Student


"ddawg09" wrote:


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.


--
ddawg09
------------------------------------------------------------------------
ddawg09's Profile: http://www.excelforum.com/member.php...o&userid=31205
View this thread: http://www.excelforum.com/showthread...hreadid=514082

Calculating Percentiles of Test Scores?
 

=(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

On Sun, 19 Feb 2006 03:40:27 -0800, Gary''s Student
wrote:

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.

Calculating Percentiles of Test Scores?
 

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