The Excel RANK function returns the statistical rank of a given value, within a supplied array of values. If there are duplicate values in the list, these are given the same rank. In Excel 2010, the Rank function has been renamed as the Rank.Eq function. However, the Rank function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.
Syntax: RANK( number, ref, [order] )
|number||–||The value for which you want to find the rank|
|ref||–||An array of values containing the supplied number|
|[order]||–||An optional argument which defines whether the ref list should be ordered in ascending or descending order
The [order] argument can take the value 0 (denotes decending order) or 1 (denotes ascending order), meaning
If the [order] argument is omitted, it will take the default value of 0 (ie. descending order). Any non-zero value is treated as the value 1 (ie. ascending order)
Rank Function Examples
For an example, we have few students name and their respective marks which is looks like below:
Now, we want to rank students based on total marks by using rank function. Below two spreadsheet shows the formula we use and the output result we found.
Rank Function Errors
If you get an error from the Excel Rank function this is likely to be the #N/A error occurs if the supplied number is not present in the supplied array of values.