The Excel RANK.AVG function returns the rank of a number against a list of other other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates.
Syntax:= RANK.AVG (number, ref, [order])
The RANK.AVG function syntax has the following arguments:
- Number Required. The number whose rank you want to find.
- Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in Ref are ignored.
-
Order Optional. A number specifying how to rank number.
Example: Let’s look at some Excel RANK.AVG function examples and explore how to use the RANK.AVG function as a worksheet function in Microsoft Excel:
Syntax: =RANK.AVG(B2,$B$2:$B$17)
Result: 3
Based on the Excel spreadsheet above, the following RANK.AVG examples would return:
Syntax: =RANK.AVG(B3,$B$2:$B$17)
Result: 5
Syntax: =RANK.AVG(B4,$B$2:$B$17)
Result: 15.5
Syntax: =RANK.AVG(B5,$B$2:$B$17)
Result: 12
Syntax: =RANK.AVG(B6,$B$2:$B$17)
Result: 8
Syntax: =RANK.AVG(B7,$B$2:$B$17)
Result: 1
Syntax: =RANK.AVG(B8,$B$2:$B$17)
Result: 13
Syntax: =RANK.AVG(B9,$B$2:$B$17)
Result: 10
Syntax: =RANK.AVG(B10,$B$2:$B$17)
Result: 14
Syntax: =RANK.AVG(B11,$B$2:$B$17)
Result: 7
Syntax: =RANK.AVG(B12,$B$2:$B$17,1)
Result: 1.5
Syntax: =RANK.AVG(B13,$B$2:$B$17,1)
Result: 6
Syntax: =RANK.AVG(B14,$B$2:$B$17,1)
Result: 12
Syntax: =RANK.AVG(B15,$B$2:$B$17,1)
Result: 15
Syntax: =RANK.AVG(B16,$B$2:$B$17,1)
Result: 12
Syntax: =RANK.AVG(B17,$B$2:$B$17,1)
Result: 8
Note:
- If Order is 0 (zero) or omitted, Excel ranks number as if ref were a list sorted in descending order.
- If Order is any nonzero value, Excel ranks number as if ref were a list sorted in ascending order.
- If the supplied number is not present within the supplied ref, RANK.AVG returns the #N/A error value.
- If the values in the supplied ref array are text values, RANK.AVG returns the #N/A error value.