Rank Numbers By Numerical Value With Excel S Rank Function

Example of the RANK Function

The image below shows an example of the RANK function in action. For the series of values 1, 6, 5, 8, and 10 in rows 2 and 3, the number 5 has a rank of:

4 because it is the fourth-largest number in the list (see row 2).2 because it is the second-smallest number in the list (see row 3).

Neither ranking matches its position as the third value from either end. However, a number’s rank will match its position in a list if the list is sorted to match the order of ranking.

RANK Function’s Syntax and Arguments

The function syntax refers to the way the function is stated and includes the function name, brackets, comma separators, and arguments. The syntax for the RANK function is:

Number is number to be ranked. This can be the actual number (see row 4 in the example above) or the cell reference to the location of the data (see rows 2 and 3). Ref is the array or range of cell references pointing to the list of numbers to use in ranking the Number argument. If non-numeric values are present in the range, these are ignored. In row 5 of the example, the number 5 is ranked first because it is the largest of the two numbers in the list. Order is a numeric value that determines whether the Number argument is ranked in ascending or descending order. Set the Order to 0 or omit it to rank in descending order. Nonzero values rank in ascending order.

Enter RANK Functions in Excel

Since Excel 2010, the RANK function cannot be entered using the dialog box. Instead, it must be entered manually. In this case, enter =RANK(C2,A2:E2,0) into cell F2 of the worksheet: This simple formula references cell C2 as the number to be ranked (first argument), specifies cells A2 to E2 as the range (second argument), and sorts in descending order (third argument). The Number argument 5 in rows 2 to 6 have the following rankings:

Row 2: Fourth. It is the fourth-largest number when the Ref range is ranked in descending order.Row 3: Second. It is the second-smallest number when the Ref range is ranked in ascending order.Row 4: Fourth. It is the fourth-largest number when the Ref range is ranked in descending order.Row 5: First. It is the larger of the two numbers when the Ref range is ranked in descending order.Row 6: #N/A. The number 5 is not in the range A6 to E6.