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
Related posts:
- Excel Function “AND” AND function use...