Find A Value's percentile

  • Thread starter Thread starter Tanya
  • Start date Start date
T

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
 
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
 
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.
 
Back
Top