In the example shown, the formula in D5 is: where “points” is the named range C5:C12.
Controlling rank order
The rank function has two modes of operation, controlled by the order argument. To rank values where the largest value is ranked #1, set order to zero (0), or omit the order argument. To rank values so that the smallest value receives rank #1, set order to 1. For example, with the values 1-5 in the range A1:A5: Set order to zero (0) when you want to rank something like top sales where the largest sales numbers should get the best rank, and set order to one (1) to rank something like race results where the shortest (fastest) times should rank highest.
Duplicates
When values to rank contain duplicates (ties), the RANK.EQ function will assign the higher rank to each set of duplicates. To assign an average rank to duplicates, see the RANK.AVG function.
Notes
The default for order is zero (0). If order is 0 or omitted, the largest value is ranked #1. If order is 1, the smallest value is ranked #1. It is not necessary to sort the values in the list before using the RANK.EQ function. In the event of a tie RANK.EQ will assign the higher rank to each set of duplicates. Some documentation suggests ref can be an array, but it appears ref must be a range.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
