Do you need to compare values in a data set? If so, you may want to consider the rank EQ function in Microsoft Excel 2010. This function allows you to determine how a specific value ranks in reference to a list of data. Whether you want to rank values from highest to lowest, or vice versa, the rank EQ function makes the process easy.
To begin, make sure your data is cleaned and formatted for analysis. Then, identify the value you want ranked, the reference distribution you want the number compared to, and whether you want to rank the values assuming the largest or smallest value as ranked first.
The rank EQ function takes the following form: enter the function name rank EQ, then inside the parentheses are three arguments. The first argument is the number, which refers to the cell containing the value you want ranked. The second argument is ref, which refers to the reference distribution you want the number compared to. Be careful when specifying the ref argument, as this may require an absolute reference. The last argument is order, which is optional. If omitted or set to zero, Excel ranks the values assuming that the largest value is ranked first. If order is set to one, then Excel ranks the values assuming that the smallest or lowest value is ranked first.
Let’s use the rank EQ function to rank the scores of several golfers in the professional golf league championship series. We’ll only rank the first 10 player scores for now. To begin, click on cell I2 and enter the header “Rank.” Then, in cell I3, enter the following formula: =RANK.EQ(H3,H3:H12). Make this an absolute reference with respect to rows and copy this formula down to cell I12. You can see that Excel has ranked the players in descending order so that the player with the highest score is number 1 and the player with the lowest score is 10th.
However, in golf, lower scores are better, so our rankings are really the reverse of what we want. To correct this, click on cell J2 and enter the header “Rank – a four ascending order.” Then, in cell J3, enter the following formula: =RANK.EQ(H3,H3:H74,1). Make this an absolute reference with respect to rows and copy this formula down to cell J12.
Now, you can see that the player with the lowest score is first and we have 4-way ties for 3rd and 7th position. This is a great example of how the rank EQ function can be used in Microsoft Excel to compare values in a data set and rank them accordingly.