Excel find the percentile of a number using PERCENTRANK.EXC function

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.

 

Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

Your email address will not be published. Required fields are marked *