Results 1 to 3 of 3

Can someone explain the percentile function

This is a discussion on Can someone explain the percentile function within the Excel Questions forums, part of the Question Forums category; Dont know what it does and microsoft help doesnt help much. An example of how it calulates and whta its ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91

    Default

    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. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default

    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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

    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 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com