Problem: I have four writers working on a project. Each week, I need to report how many pages they have written toward their goal. I want to add a formula to rank them in high-to-low order.
- Rank the scores.
Strategy: If you are not concerned about ties, you can use the RANK or RANK.EQ function. This function requires two arguments; the cell to be ranked and the range in which to rank the cell. In plain language, you are asking the function to assign a rank to the value in C2 among all values in C2:C5.
- Assign a rank.
Note that the C$2:C$5 range in the second parameter uses dollar signs so the formula can be copied down but the range of scores remains the same.
The RANK and RANK.EQ functions are identical. RANK will work in any version of Excel. RANK.EQ will only work in Excel 2010 or newer. RANK was renamed to RANK.EQ in Excel 2010 to differentiate it from RANK.AVG. For a comparison of these functions, see the next topic.
Additional Details: There is an optional third argument. If you don't specify the third argument, the values are ranked in high-to-low order. Sometimes you might need to rank in a low-to-high fashion. Golf is one such instance. In such cases, use a 1 for the third argument of the RANK or RANK.EQ function.
See Also: "Rank a List Without Ties," "Sorting with a Formula"