Subscribe!

Finding and displaying the percentile of specific results can be valuable to your audience. Sometimes stand alone numbers do not tell the story, but, if I know that 80% of the other results are lower than me, that tells a more complete story.

The percentile is defined as the value of a variable below which a certain percent of observations fall. For example, if you score in the 80th percentile on a test, 80% of the other test takers did worse than you. In this test taking scenario, if you score a C on your test, that could be good or bad, it needs to be compared to the rest of the group. If you score a C and you are in the 90th percentile, that shows you did relatively better than everyone else.

Luckily, Excel makes it easy for us to calculate the percentile using the =percentrank() function:

Syntax:

PERCENTRANK(array,x,significance)
Array: is the array or range of data you are finding the results for
X: is the value for which you want to know the rank.
significance: is the number of digits returned (ie 90th percentile or 93rd percentile)

Example:
Your range of values is in A1:A12, the first value you want to find the percentile for is in A! and you want only 1 significant digit:

=PERCENTRANK($A$1:$A$12,A1,1)*100

Multiplying by 100 gets the number to the commonly accepted percentile.

Joseph Harris
Excel Help
More Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. How to Use ISERROR Function in Excel The ISERROR function is a versatile one that is very...

This entry was posted on Tuesday, January 6th, 2009 at 2:39 pm and is filed under Business Analyst, Functions. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.