The ‘RANK’ formula in Excel is used to rank numerical values within a list.
This formula ranks an input number with reference to a larger list of numbers, in either ascending or descending order. This functionality could be useful in any scenario where numbers need to be ranked. For instance, you could use it to establish the top selling products that a company sells in a given year, or to identify the top performing salesmen in a particular region.
Note: in the event of a tie (i.e. where the reference list contains duplicate items), the ranking system will assign the lower rank value to the set of duplicates. For instance, you may have a scenario where there are items ranked 1,2,2,4 rather than 1,2,3,4.
- number – The input number to rank
- ref – The array of numerical cells against which you want to rank your input number (i.e. the full list of numbers you want to select).
- [order] – Select either 1 to rank in ascending order (lowest number would be ranked #1), or 0 to rank in descending order (highest number would be ranked #1). If no option is selected, the option defaults to descending.
The RANK function returns a numerical value, indicating a rank.