What percentile each score is in (Percent Rank)?

Can you show what results you want ?

Percent rank seems to work for me

I guess that I am confused, we received this from a college and the formula they used for percentrank is =(((COUNT($A$2:$A$18)+1+RANK(A2,$A$2:$A$18,1)-RANK(A2,$A$2:$A$18,0))/2)-0.5)/COUNT($A$2:$A$18) which gives a different rank below?

Not sure why, can anyone help?

I got the answer, thanks for looking at this post. For anyone interested I got a link listed below and an exerpt to the link below that.

Thanks again Stephen.

http://www.coventry.ac.uk/ec/~nhunt/pottel.pdf

Excel also has a strange way to calculate ranks and percentiles. Excel’s built-in RANK
function does not take into account ‘tied’ ranks. For example, in a series of measurements
100, 120, 120, 125 Excel gives two times rank 2 to the value of 120 and value 125 gets the
rank 4. When ‘tied’ ranks are taken into account, the rank of 120 should be (2 + 3)/2 = 2.5 and the value of 125 should indeed get rank 4. Excel assigned the lowest of the two ranks to both observations, giving each a rank of 2. Because Excel doesn’t consider ‘tied’ ranks it is impossible to calculate the correct non-parametric statistics from the obtained ranks. For this reason I developed a User Defined Function, called ‘RANKING’, which takes into account ‘tied’ ranks.


Function Ranking(V As Double, R As Range) As Double
Dim No As Integer
Ranking = Application.WorksheetFunction.Rank(V, R, 1)
No = Application.WorksheetFunction.CountIf(R, V)
Ranking = Ranking + (No - 1) / 2
End Function

The way Excel calculates percentiles is also not the way most statistical packages calculate them. In general, the differences are most obvious in small data sets. As an example, let’s take the systolic blood pressures of 10 students sorted in ascending order: 120, 125, 125, 145, 145, 150, 150, 160, 170, 175. The lower quartile (or 25% percentile) as calculated with Excel’s built-in function QUARTILE (or PERCENTILE) is 130 and the upper quartile is 157.5. A statistical package, however, will give 125 and 170 as lower and upper quartile, respectively. Apparently, Excel calculates the lower quartile 130 = 125 + (145-125)*0.25 and the upper quartile as 157.5 = 150 + (160-150)*0.75. This is an interpolation between the values below and above the 25% or 75% observation. Normally, the pth percentile is obtained by first calculating the rank l = p(n+1)/100, rounded to the nearest integer and then taking the value that corresponds to that rank. In case of lower and upper quartiles, the ranks are 0.25*(10+1) = 2.75 Þ 3 and 0.75*(10+1) = 8.25 Þ 8 which corresponds to 125 and 170 resp.

Are you sure about the last example? The 8th value (upper quartile) appears to be 160 rather than 170 if I am not mistaken. Dirk