Percentile Rank Formula

Percentile rank in Excel gives the rank percentile of a given list. In normal calculations, we know the formula is R = p/100(n+1). However, in Excel, we use the RANK.EQ function with the COUNT function to calculate the rank percentile of a given list.

For example, consider the below table.

Let us find the percentile rank with formula, =PERCENTILE.EXC(B2:B11,0.75)

Press Enter key.

We can see the result as shown in the below image.

Likewise, we can find the percentile rank in Excel.

In this article, let us learn how to use percentile rank in Excel with detailed examples.

• The percentile rank formula calculates the rank percentile of a list, which is typically calculated using the RANK.EQ function and the COUNT function, rather than the standard formula R = p/100(n+1).
• Percentile ranks are essential in statistics and education to provide relative rankings to students, allowing them to understand how a score compares to other values or observations in a dataset or distribution.
• The percentile rank is a relative ranking of stocks used by financial analysts to screen stocks based on key metrics like the 90th percentile, rather than relying on mean or actual values rather than absolute values.

The percentile rank is the percentage of scores equal to or could be less than a given value or score. The percentile-like percentage also falls within the range of 0 to 100. Mathematically, it is represented as:

R = P / 100 (N + 1)

Where,

• R = Percentile Rank
• P = Percentile
• N = Number of Items

The formula depicts how many scores or observations fall behind a particular rank. For example, one observation gets 90 percentile; it does not mean the observation score is 90% out of 100. But rather, it states that the observation has performed at least what other 90% of observations are or is above those observations. Hence, the formula incorporates the number of observations, multiples it with the percentile, and provides the position where that observation would lie. So, after the data is arranged from lowest to largest and rank is supplied to each observation, we can only use the number derived from the formula and conclude that observation lies at the asked percentile.

Example #1

Consider a data set of following numbers: 122, 112, 114, 17, 118, 116, 111, 115, 112. You are required to calculate the 25th percentile rank.

Solution:

Use the following data for the calculation of percentile rank.

So, the calculation of rank can be done as follows:

R = P/100(N+1)

= 25/100(9+1)

The rank will be:

Rank = 2.5^th rank.

The percentile rank will be:

Since the rank is an odd number, we can take an average of 2nd term and 3rd term, which is (111 + 112)/ 2 = 111.50.

Example #2

William, a well-known animal doctor, is currently working on the health of elephants and is in the process of creating medication to treat elephants from a common disease they suffer from. But for that, he first wants to know the average percentage of elephants that fall below 1185.

• For that, he has collected a sample of 10 elephants, and their weight in kgs is as follows:
• 1155, 1169, 1188, 1150, 1177, 1145, 1140, 1190, 1175, 1156.
• Use the percentile rank formula to find the 75th percentile.

Solution:

Use the following data for the calculation of percentile rank.

So, the calculation of rank can be done as follows:

R= P / 100 (N + 1)

=75 / 100 (10 + 1)

The rank will be:

Rank= 8.25 rank.

The percentile rank will be:

The 8^th term is 1177 and now adding to this 0.25 * (1188 – 1177) which is 2.75, and the result is 1179.75.

Percentile Rank = 1179.75

Example #3

IIM institute wants to declare their result for each student in relative terms. They have come out with the idea that instead of providing percentages, they want to give a relative ranking. The data is for the 25 students. Using the percentile rank formula, find out what will be the 96th percentile rank?

Solution:

The number of observations here is 25. So our first step would be arranging data rank-wise.

So, the calculation of rank can be done as follows:

R= P/100(N+1)

= 96/100(25+1)

= 0.96*26

The rank will be:

Rank =24.96 rank

The percentile Rank will be:

The 24^th term is 488 and now adding to this 0.96 * (489 – 488) which is 0.96, and the result is 488.96.

Example #4

Let us now determine the value through the Excel template for practical example I.

Solution:

Use the following data for the calculation of percentile rank.

So, the calculation of percentile rank can be done as follows:

The percentile rank will be:

Percentile Rank = 1179.75

The percentile ranks are useful when someone wants to understand how a particular score will compare to the other values, observations, or scores in a given dataset or a given distribution of scores. Percentiles are mostly used in statistics and education, where instead of providing relevant percentages to the students, they show them relative rankings. And if one is interested in the relative ranking, then mean, actual values, or the variance, which is the standard deviation, will not be useful.

• The percentile rank is the percentage equal to or could be less than a given value or score.
• Percentile rank gives you the picture relative to others, always not an absolute value or answer concerning other observations and not about mean.
• Further, some financial analysts use this criterion to screen the stocks where they could be using any of the financial key metrics and picking the stock that lies in the 90th percentile.

This article is a guide to the Percentile Rank Formula. We learned how to calculate percentile rank in Excel with practical examples and a downloadable Excel template. You can learn more about Excel modeling from the following articles: -

• Formula of Percentile Excel
• RANK in Excel
• Midrange Formula
• Formula of Standard Normal Distribution
• Excel MAXIFS