July 2, 2022

# How to use RANK.AVG Function in Excel

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.

READ:  How to use QUARTILE.EXC Function in Excel #### Excel

View all posts by Excel →