Rank in statistics is the ordinal number of value relative to other values in a list arranged in a specified order (e.g., ascending or decreasing). For example: to get the rank of number 7 in the data sample: 10, 3, 5, 7, 9, 14, 103, you need to sort the data sample in the order from smallest to largest: 3, 5, 7, 9, 10, 14, 103. The rank of number 7 in ascending rank is 3.
In Excel, you can use the RANK.AVG function to find the rank order in a list of numbers: descending or ascending. When there is more than one value having the same rank, RANK.AVG function will return to the average.
Formula:
= RANK.AVG(Number, Ref, [Order])
Explanations:
– The number is required, which is the number whose rank you are looking for;
– Ref is required, which is the data range you search for;
– [Order] is optional, which is the method to decide how to rank. When the order is 0 or omitted, the list is sorted in the descending order; when order is nonzero value, the list is sorted in the ascending order.
Cautions:
– Only numeric values in the "Ref list" are counted, and the nonnumeric values are ignored.
– When there are more than 1 values having the same rank, it returns to the average.
Example 1: What is the Rank of income $73,069 in descending order in Column C (descending order)?
= RANK.AVG(73069,C2:C9) The rank of income $73,069 is 4 in descending order.
Example 2: What is the Rank of income $73,069 in ascending order in Column C (ascending order)?
= RANK.AVG(73069,C2:C9,1) The rank of income $73,069 is ranked 5 in ascending order.
