This Excel tutorial explains how to find the percentile of a number from an array using PERCENTRANK.EXC function.
Excel Percentile
Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the location of observation at 40% of total data.
Percentile is commonly used in salary survey to find out the 25th 50th, 75th, 90th percentile. 50% percentile is known as median, human resources department always compares P50 salary with employees’ salary so that they know who are below market compensation. Some companies may have salary guideline of P75, which means they generally pay better than the market in order to attract and retain talents. Percentile is a better measure than average because extreme data are disregarded using percentile.
Find the percentile of a number using PERCENTRANK.EXC function
In my previous post, I have demonstrated how to calculate the number given a percentile.
Suppose data set 1, 3, 5, 7 lie in Range A1:A4. To find tne number at Percentile 75,
= PERCENTILE.EXC(A1:A4,0.75)
= 6.5
In this post I am going to the opposite. Function PERCENTRANK.exc (Percent Rank) is to claculate percentile given a number.
Syntax of PERCENTRANK.EXC function
PERCENTRANK.EXC(array,x,[significance])
| Array | Required. The array or range of data with numeric values that defines relative standing |
| X | Required. The value for which you want to know the rank. |
| Significance | Optional. A value that identifies the number of significant digits for the returned percentage value. If omitted, PERCENTRANK.EXC uses three digits (0.xxx). |
Example of PERCENTRANK.EXC function
Let’s say our company has 4 staff, their salary are as follows
I want to know if salary $25000 is high or low in the company.
=PERCENTRANK.EXC(B2:B5,25000)
= 0.5 (50th percentile)
Note that there is another similar function called PERCENTRANK.INC, the result may be slightly different but both of them are considered as correct.
