Thanks:  0
Likes:  0

# Thread: Can someone explain the percentile function

1. Dont know what it does and microsoft help doesnt help much. An example of how it calulates and whta its used for would be great

Thanks for the help

2. Percentile is not Excel's most-oftenly used function, so to anyone reading this, please chime in with where my understanding of it is incorrect.

As you know, a percentile is a relative standing. It describes where a number (such as a student's test score, or a company's price earnings ratio) ranks among other students' test scores, or other companies' price earning ratios.

The percentile function confuses some people, because it does not return a percentile rank. Instead, it returns the value of what a given percentile rank would be.

Why would someone want to know what the Percentile rank would return? Usually, in order to set (or know ahead of time) a threshold of acceptability or rejection of a given percentile's rank among a range of numbers (such as test scores or PERs).

Here's an example of how the Percentile function can be used, taken right out of Patrick Blattner's book, "Microsoft 2000 Functions in Practice" (which in my opinion, ranks in the 80th percentile of Excel books...I've read worse, but not bad):

Range D5:D16 contains the price earnings ratios of 12 different companies. Those ratio numbers are (starting in D5) 7, 16, 17, 18, 19, 19, 20, 22, 31, 34, 38, 60.

In F6 is a percentile number that you enter. Let's say you enter the number 0.7, to represent 70%.

The syntax is PERCENTILE(Range, xValue)

In G6 you enter the formula
=PERCENTILE(D5:D16,F6).
This would return the number 28.3

What G6 now demonstrates is that the price earnings ratio of 70% of the companies are less than 28.3, and the other 30% of the companies have a price earnings ratio of greater than 28.3.

Hope this helps.

3. Since QUARTILE is just a specialized version of PERCENTILE... the 1st quartile is equivalent to the 25th percentile and the 3rd quartile is equivalent to 75th percentile... perhaps, you'd be interested in the algorithm used for calculation of the QUARTILE function. If so, see http://support.microsoft.com/default...;en-us;Q103493.

[ This Message was edited by: Mark W. on 2002-04-16 07:33 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•