Find A Value's percentile

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tanya - 25 Oct 2007 16:29 GMT

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

Reply to this Message

Mike Middleton - 25 Oct 2007 19:42 GMT

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

>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
[quoted text clipped - 3 lines]
> series of numbers falls into. Thanks for your help,
> Tanya

Reply to this Message

Bernard Liengme - 25 Oct 2007 20:43 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Reply to this Message

Lori - 26 Oct 2007 10:45 GMT

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.

> 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
[quoted text clipped - 3 lines]
> series of numbers falls into. Thanks for your help,
> Tanya

Reply to this Message