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.

 

Add a Comment

Your email address will not be published. Required fields are marked *