FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Percentile Rank Formula

Updated on May 6, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Percentile Rank In Excel?

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.

Key Takeaways

• 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.

Percentile Rank Formula() Excel

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)

For eg:
Source: Percentile Rank Formula (wallstreetmojo.com)

Where,

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use Percentile Rank Formula In Excel?

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.

Examples

You can download this Percentile Rank Formula Excel Template here – Percentile Rank Formula Excel Template

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.5th 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 8th 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 24th 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

Relevance And Uses Of Percentile Rank Formula

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.

Important Things To Note

• 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.

1. What are the limitations of percentile rank?

The percentile rank formula has limitations as it doesn’t represent equal intervals, making it insufficient for comparing achievement among student groups.

2. What is the significance of percentile rank?

Percentile ranks are a crucial tool in test theory, indicating the percentage of examinees in the norm group who scored below the score of interest, providing a clearer interpretation of standardized test scores.

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.

3. What is difference between percentile and percentile rank?

Each percentile represents a point, while each percentile rank covers an entire interval. Starting with a proportion or percentage, a percentile is computed using a raw score, while a percentile rank is computed between 0 and 100.