Can someone explain the percentile function

drag-driver · Apr 16th, 2002, 06:46 AM

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

Tom Urtis · Apr 16th, 2002, 07:49 AM

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.

Mark W. · Apr 16th, 2002, 08:31 AM

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 ]

Apr 16th, 2002, 07:49 AM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	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. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 06:46 AM	#1
drag-driver Board Regular Join Date: Feb 2002 Posts: 91	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

Apr 16th, 2002, 08:31 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]