Table of Contents
In Excel, you can rank values by group by using the RANK.EQ function. This function takes three arguments: the value to be ranked, the range of values to be ranked against, and the order in which to rank the values (ascending/descending). This function returns the rank of the value compared with the other values in the range. You can use this function to rank values by group, such as ranking students in a class by score or ranking products by sales in each region.
You can use the following formulas to rank values by group in Excel:
Formula 1: Rank Values by Group
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13>B2))+1
This particular formula finds the rank of the value in cell B2 that belongs to the group in cell A2.
This formula assigns a rank of 1 to the largest value, 2 to the second largest value, etc.
Formula 2: Rank Values by Group (Reverse Order)
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13<B2))+1
This particular formula finds the reverse rank of the value in cell B2 that belongs to the group in cell A2.
This formula assigns a rank of 1 to the smallest value, 2 to the second smallest value, etc.
The following examples show how to use each formula in Excel.
Example 1: Rank Values by Group in Excel
Suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
We can use the following formula to rank the points by team:
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13>B2))+1
We’ll type this formula into cell C2, then copy and paste the formula down to every remaining cell in column C:
- The player with 22 points for the Mavs has the 2nd largest points value among Mavericks players.
- The player with 28 points for the Mavs has the 1st largest points value among Mavericks players.
- The player with 31 points for the Spurs has the 2nd largest points value among Spurs players.
And so on.
Example 2: Rank Values by Group (Reverse Order) in Excel
Once again suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
We can use the following formula to rank the points in reverse order by team:
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13<B2))+1
We’ll type this formula into cell C2, then copy and paste the formula down to every remaining cell in column C:
Here’s how to interpret the values in column C:
- The player with 22 points for the Mavs has the 3rd smallest points value among Mavericks players.
- The player with 28 points for the Mavs has the 4th smallest points value among Mavericks players.
- The player with 31 points for the Spurs has the 4th smallest points value among Spurs players.
And so on.