Using Excel Rank Formula: A Step-by-Step Guide
Excel is a powerful tool for data analysis and manipulation. One of the many functions that Excel offers is the RANK.EQ function, which can be used to determine the rank of a number in a list. However, this function has some limitations, such as not being able to handle cases where there are ties for a particular rank. In such cases, the RANK.EQ function assigns the same rank to all the tied numbers, which can be misleading.
To overcome this limitation, you can use a custom Excel formula that takes into account ties and assigns the same rank to all the tied numbers. In this article, we will provide a step-by-step guide on how to use this custom formula to rank numbers in Excel.
The Custom Excel Rank Formula
The custom Excel rank formula we will be using is:
=IF(M2=0,"",(M2>0,SUMPRODUCT(($B$2:$B$162=B2)*($C$2:$C$162=C2),(M2<= $B$2:$B$162)/COUNTIF($B$2:$B$162,$B$2:$B$162))))This formula may look complicated, but it is actually quite simple once you break it down. Let's take a closer look at each part of the formula.
Breaking Down the Custom Excel Rank Formula
IF(M2=0,"",(M2>0,...))
The IF statement checks if the value in cell M2 is equal to zero. If it is, the formula returns an empty string. If it is not, the formula proceeds to calculate the rank using the SUMPRODUCT and COUNTIF functions.
SUMPRODUCT(($B$2:$B$162=B2)*($C$2:$C$162=C2),(M2<=$B$2:$B$162)/COUNTIF($B$2:$B$162,$B$2:$B$162))
The SUMPRODUCT function is used to calculate the rank by taking into account ties. It multiplies two arrays and then adds up the products. The first array is created using the ($B$2:$B$162=B2)*($C$2:$C$162=C2) expression, which checks if the values in columns B and C match the values in columns B2 and C2, respectively. If they do, the expression returns 1; otherwise, it returns 0. The second array is created using the (M2<=$B$2:$B$162)/COUNTIF($B$2:$B$162,$B$2:$B$162) expression, which checks if the value in cell M2 is less than or equal to the values in column B. If it is, the expression returns the corresponding value in the array; otherwise, it returns 0. The SUMPRODUCT function then adds up the products of the two arrays, which gives the rank of the value in cell M2.
Using the Custom Excel Rank Formula
Now that we have a better understanding of the custom Excel rank formula, let's see how we can use it to rank numbers in Excel.
1. First, enter the values you want to rank in a column (e.g., column B).
2. Next, enter another column (e.g., column C) with the same values as column B. This column will be used to handle ties.
3. In the first cell of the column where you want to display the ranks (e.g., column D), enter the custom Excel rank formula.
4. Copy the formula down the column to calculate the ranks for all the values in column B.
- Excel's built-in formula for ranking numbers (
RANK.EQ) does not handle ties well. - You can use a custom Excel formula to rank numbers and handle ties.
- The custom Excel rank formula is:
=IF(M2=0,"",(M2>0,SUMPRODUCT(($B$2:$B$162=B2)*($C$2:$C$162=C2),(M2<= $B$2:$B$162)/COUNTIF($B$2:$B$162,$B$2:$B$162)))) - The custom Excel rank formula takes into account ties and assigns the same rank to all the tied numbers.
- To use the custom Excel rank formula, enter the values you want to rank in a column, enter another column with the same values, and enter the formula in the first cell of the column where you want to display the ranks. Copy the formula down the column to calculate the ranks for all the values in the first column.