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.

## Like this thread? Share it with others