RANK Function in Excel

RANK Function in Excel

4 mins readComment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 19, 2024 16:45 IST

The RANK function in Excel assigns a numerical rank to a value in a dataset based on its position relative to other values. It helps determine whether a value is the highest, lowest, or somewhere in between. The RANK function is useful for analysing data and identifying individual values' relative importance or standing within a set. Additionally, it assigns the same rank to duplicate numbers, providing a clear hierarchy of values. Let us explore more.

RANK Function in Excel

 

What is the RANK function in Excel?

The RANK Function is categorized as an Excel Statistical function. It returns the rank of a given number in a list of numbers.

RANK Functions in Excel

The RANK functions in Excel are powerful tools for determining the relative position of a value within a dataset. Let's explore each variant:

1. RANK Function

The RANK function in Excel returns the rank or position of a numeric value compared to other values in the same list. It tells you where a value stands in relation to others.

Syntax:

=RANK(number, ref, [order]) 

Where,

  • Number: The value whose rank you want to find.
  • Ref: The list of numeric values to rank against.
  • Order (optional): Determines the ranking order:
    • 0 or omitted: Rankings in descending order.
    • 1 or any non-zero value: Rankings in ascending order.

2. RANK.EQ Function

RANK.EQ is an improved version of the original RANK function, introduced in Excel 2010. The syntax of RANK.EQ is the same as that of the RANK function. 

RANK.EQ follows the same logic as the original RANK function regarding handling tied ranks. If several values are ranked equally, all such values are assigned the highest rank. EQ stands for "equal".

Syntax:

=RANK.EQ(number, ref, [order]) 

3. RANK.AVG Function

The RANK.AVG function returns the average rank if multiple numbers share the same rank. The difference is that the average rank is returned if more than one number has the same rank. AVG stands for 'average'.

Syntax:

=RANK.AVG(number, ref, [order]) 

Note - RANK has been replaced by RANK.EQ and RANK.AVG for improved functionality and precision. RANK.EQ and RANK.AVG offers more versatility and consistency in handling tied ranks than the original RANK function. While RANK still works in Excel 2016 for backward compatibility, it may not be available in future versions of Excel. 

The RANK function in Excel 2016 typically shows a yellow triangle with an exclamation point, indicating a compatibility issue or potential future problem.

Rank function vs others

Example 1 - RANK Function in Excel

Suppose we have a list of students' marks in a particular subject, and we want to rank them based on their scores. Here's a step-by-step guide:

Step 1: Organize Your Data

Ensure your data is organised accurately. You might have a column for student names and another column for their corresponding marks.

Step 2: Choose a Cell to Display Rankings

Select a cell where you want to display the ranks. This could be in a separate column next to the marks data.

Step 3: Enter the RANK Formula

In the selected cell, enter the RANK formula. 

=RANK(G2,$G$2:G$11,0)

RANK Function in Excel

Copy this formula down to rank the rest of the marks.

Step 5: View Rankings

Once you have entered the formula, the rankings appear next to each mark. These rankings represent the position of each mark relative to the other marks in the dataset.

RANK Function in Excel 2

The RANK function is incredibly useful for analysing student performance, identifying top performers, and understanding the distribution of scores in a dataset. 

Note: Please note that the range you provide for the RANK function is the absolute reference, so the range won't change when the formula is copied to other cells. This is essential to maintain consistency in the calculation. The optional order argument is not provided since RANK will assign 1 to the largest value by default.

Example 2 - Handling Ties by the RANK Function

In Excel, when the RANK function encounters a tie, meaning two or more values in the dataset have the same value, it assigns the same rank to all tied values. Here's how it works:

For example, if two students score 343 on a test, it creates a tie situation.

  • When the RANK function encounters a tie, it assigns the same rank to all tied values.
  • For instance, if there's a tie for the second-highest score, both students receive a rank of 5, and the next rank assigned is 7, not 6.
Tied ranks
  • Excel does not skip ranks in the case of a tie; instead, it ensures each tied value receives the same rank.

RANK Function: Key Points

  • RANK functions in Excel only work for numeric values.
  • They return the same rank for duplicate values, skipping subsequent ranks.
  • In Excel 2010 and later versions, RANK has been replaced by RANK.EQ and RANK.AVG for better functionality.
  • If the specified number is not found within the reference list, the function returns the #N/A error.

Recommended Articles

Consider checking these useful functions in MS Excel –

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio