Q: I have four writers working on a project. Each week, I need to report how many pages they have written toward their goal. How can I add a formula to my tracking sheet that will allow me to rank them in high-to-low order (Figure 1)?
A: 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 (Figure 2).
However, take note that the C$2:C$5 range in the second parameter uses dollar signs so the formula can be copied down, while the range of scores remains the same. The RANK and RANK.EQ functions are identical. RANK was renamed RANK.EQ in Excel 2010 to differentiate it from RANK.AVG. RANK will work in any version of Excel, but RANK.EQ will work only in Excel 2010 or newer.
In addition, 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.
If you would like to submit a question to Bill “MrExcel” Jelen, go to CFO’s Spreadsheet Community Center at www.cfo.com/spreadsheets.