Unlike Excel, Google Sheets offers a convenient way to rank group wise by combining the RANK and FILTER functions. In this article, we will explore how to use this powerful combination to rank groups in Google Sheets, regardless of whether the data is sorted or unsorted.
Rank Group Wise in Google Sheets (Rank Within Group)
To begin, let’s take a look at a sample data set in Google Sheets. The first column represents the groups, while the second column contains the numbers to rank.
To rank the numbers within each group, we can use the following formula in cell D2, which can then be copied down from D3 to D8:
=Rank(B2,Filter(B$2:$B$8,A$2:$A$8=A2),0)This formula compares the value in B2 to the filtered values in column B, where the corresponding values in column A match the current row’s group. The last parameter, set to 0, ensures that the highest value within each group receives rank 1.
If you prefer the lowest value within each group to have rank 1, you can modify the formula by changing the last parameter from 0 to 1:
=Rank(B2,Filter(B$2:$B$8,A$2:$A$8=A2),1)By applying this formula, you can easily rank the numbers within groups, regardless of the sorting of the original data.
RANK + Filter Combo Formula Explanation
When the formula in cell D2 is copied down, it adjusts accordingly for each row. For example, let’s consider the formula in cell D4.
In this case, the formula filters column B based on the values in column A, specifically when column A is “Group B”. For the fourth row, the filtered values are 38 and 92.
Thus, the formula can be interpreted as follows, returning a rank of 2 for this row:
=rank(38,{38;92},0)Conclusion
The combination of the RANK and FILTER functions offers a significant advantage over traditional formulas, such as SUMPRODUCT, commonly used in Excel. With this approach, you can easily adjust the ranking order by modifying the last parameter in the formula from 0 to 1 or vice versa.
Furthermore, this formula remains effective even if the group categories in column A are not sorted. With the power of Google Sheets, you can now effortlessly rank groups based on specific criteria. For further guidance and insights on Google Sheets, be sure to visit Crawlan.com.
