Tuesday, November 08, 2011

RANK FORMULA IN EXCEL



Values
Ranking Position
High to Low
7
4
 =RANK(C4,C4:C8)
4
5
 =RANK(C5,C4:C8)
25
1
 =RANK(C6,C4:C8)
8
3
 =RANK(C7,C4:C8)
16
2
 =RANK(C8,C4:C8)
Values
Ranking Position
Low to High
7
2
 =RANK(C11,C11:C15,1)
4
1
 =RANK(C12,C11:C15,1)
25
5
 =RANK(C13,C11:C15,1)
8
3
 =RANK(C14,C11:C15,1)
16
4
 =RANK(C15,C11:C15,1)
Values
Ranking Position
High to Low
10
5
 =RANK(C18,C18:C22)
30
2
 =RANK(C19,C18:C22)
20
4
 =RANK(C20,C18:C22)
30
2
 =RANK(C21,C18:C22)
40
1
 =RANK(C22,C18:C22)
What Does It Do ?










This function calculates the position of a value in a list relative to the other values in the list.
A typical usage would be to rank the times of athletes in a race to find the winner.
The ranking can be done on an ascending (low to high) or descending (high to low) basis.
If there are duplicate values in the list, they will be assigned the same rank. Subsequent ranks
would not follow on sequentially, but would take into account the fact that there were duplicates.
If the numbers 30, 20, 20 and 10 were ranked, 30 is ranked as 1, both 20's are ranked as 2, and
the 10 would be ranked as 4.
Value
Rank
30
1
 =RANK(B34,B34:B37)
20
2
 =RANK(B35,B34:B37)
20
2
 =RANK(B36,B34:B37)
10
4
 =RANK(B37,B34:B37)
Syntax









=RANK(NumberToRank,ListOfNumbers,RankOrder)
The RankOrder can be 0 zero or 1.
Using 0 will rank larger numbers at the top. (This is optional, leaving it out has the same effect).
Using 1 will rank small numbers at the top.
Formatting








No special formatting is needed.
Example









The following table was used to record the times for athletes competing in a race.
The =RANK() function was then used to find their race positions based upon the finishing times.
Athlete
Time
Race Position
Abhay
01:30
4
 =RANK(C53,C53:C58,1)
Karan
01:45
6
 =RANK(C54,C53:C58,1)
Sunil
01:02
1
 =RANK(C55,C53:C58,1)
Iqbal
01:36
5
 =RANK(C56,C53:C58,1)
Dev
01:27
3
 =RANK(C57,C53:C58,1)
Arjun
01:03
2
 =RANK(C58,C53:C58,1)