RANK Function in Excel
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.
- What is the RANK function in Excel?
- RANK Functions in Excel
- Example 1 - RANK Function in Excel
- Example 2 - Handling Ties by the RANK Function
- RANK Function: Key Points
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.
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
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.
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)
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.
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.
- 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 –
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
Top Picks & New Arrivals
