Find A Value's percentile

[Tanya]

I have a list of test scores in an Excel 2007 worksheet and I want to
know what percentile each score falls into. The Percentile function is
probably involved, but I'm not sure how to incorporate it. As I
understand it that function returns values that fall within a
specified percentile, while I want to know what percentile each of a
series of numbers falls into. Thanks for your help,
Tanya

 

[Mike Middleton]

Tanya -

First, do a Google search for "percentile" so that you'll be aware of
various ways that percentiles are determined.

Second, see
http://office.microsoft.com/en-gb/excel/HP052092111033.aspx
for an explanation of Excel's somewhat-nonstandard method.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Bernard Liengme]

I have my test scores in A1:A45
In F1:F21 I have 1%, 5%, 10%, 15% ..(5%, 100%
In E1 I have the formula =PERCENTILE($A$1:$A$45,F1)
This is copied down to E22
In B1 I have the formula
=IF(A1<$E$1,"< "&TEXT($F$1,"0%"),VLOOKUP(A1,$E$1:$F$21,2))
This is copied down to B45
With the caveat in Mike M's answer, I hope this helps

 

[Guest]

Maybe you want to return the percentile rank for each score - a scale from 0%
to 100% based on relative standing. With scores in the first column fill down
in an adjacent column:

=PERCENTRANK(A:A,A2,2)

and format as percentage.