RANK(), PERCENTRANK() in Advanced Excel

In Excel, the RANK() and PERCENTRANK() functions are used for ranking a number or a set of numbers based on their relative position in a data set. These functions are useful when you want to understand the position of a particular value among a list or when you need to calculate the percentile of a number in a data range.

1. RANK() Function

The RANK() function is used to determine the rank of a number within a list of numbers. It ranks the number in the context of other numbers, either in descending or ascending order.

Syntax:

RANK(number, ref, [order])
  • number: The number whose rank you want to determine.
  • ref: The array or range of numbers in which you want to find the rank of the specified number.
  • [order]: This argument is optional. If order is 0 or omitted, the numbers are ranked in descending order. If order is 1, the numbers are ranked in ascending order.

Real-Time Example of RANK()

Scenario: You have a list of test scores for five students, and you want to rank them from highest to lowest.

        =RANK(85, A1:A5)

Explanation: The function will rank the score of 85 among the list of scores in cells A1:A5. If 85 is the highest, the function will return a rank of 1.

2. PERCENTRANK() Function

The PERCENTRANK() function returns the relative rank of a number in a data set as a percentage of the total data set. This is useful for determining the percentile of a number within a group of values.

Syntax:

PERCENTRANK(array, x, [significance])
  • array: The array or range of data.
  • x: The number for which you want to find the percentile.
  • [significance]: This argument is optional. It is the number of digits to which you want to round the result. If omitted, the default is 3 digits.

Real-Time Example of PERCENTRANK()

Scenario: You have the following set of test scores: 60, 80, 85, 90, and 95, and you want to find out the percentile of the score 85.

        =PERCENTRANK(A1:A5, 85)

Result: 0.6 or 60%

Explanation: The function calculates that the score of 85 is higher than 60% of the scores in the list. This means the score falls in the 60th percentile.

Key Differences Between RANK() and PERCENTRANK()

  • RANK(): Provides the relative position of a number in a list, with 1 being the highest or lowest (depending on order).
  • PERCENTRANK(): Gives the percentile rank of a number, representing how it compares to the other values in terms of percentage.

Real-Time Examples with More Context

1. Employee Performance Ranking

In a company, the HR department may need to rank employees based on their performance scores. For example, if you want to rank employee scores from highest to lowest, you can use the RANK() function. 

        =RANK(95, A1:A10)    Result: 1

In the above example, if 95 is the highest performance score among the 10 employees, it will be ranked as 1.

2. Exam Results Analysis

After conducting an exam, the teacher may want to know the percentile of each student's score. For example, if a student scored 75 out of 100, you can use the PERCENTRANK() function to find out what percentage of the class scored below this student. 

        =PERCENTRANK(A1:A20, 75)    Result: 0.55 or 55%

This indicates that the student's score is higher than 55% of the students in the class.

3. Sales Ranking

In sales, ranking products or employees based on sales performance can be easily done with the RANK() function. For instance, if you want to find out the rank of an employee based on sales amount, you can use RANK(). 

        =RANK(5000, B1:B10)    Result: 2

If 5000 is the second highest sales figure in the list, the employee will be ranked 2nd.

Conclusion

The RANK() and PERCENTRANK() functions in Excel are useful for determining how a number or data point compares to others in a given data set. Use RANK() when you need to assign a relative position to a number, and use PERCENTRANK() when you need to know the percentage rank or percentile of a number within a data range.




Advertisement