RANK ()
This function calculates the position of a value in a list
relative to the other values in the list.
Best view in desktop mode.
|
A
|
B
|
C
|
D
|
|
|
1
|
Values
|
Ranking Position
High to Low |
||
|
2
|
7
|
4
|
=RANK(A2,A2:A6)
|
|
|
3
|
4
|
5
|
=RANK(A3,A2:A6)
|
|
|
4
|
25
|
1
|
=RANK(A4,A2:A6)
|
|
|
5
|
8
|
3
|
=RANK(A5,A2:A6)
|
|
|
6
|
16
|
2
|
=RANK(A6,A2:A6)
|
|
|
7
|
||||
|
8
|
Values
|
Ranking Position
Low to High |
||
|
9
|
7
|
2
|
=RANK(A9,A9:A13)
|
|
|
10
|
4
|
1
|
=RANK(A10,A9:A13)
|
|
|
11
|
25
|
5
|
=RANK(A11,A9:A13)
|
|
|
12
|
8
|
3
|
=RANK(A12,A9:A13)
|
|
|
13
|
16
|
4
|
=RANK(A13,A9:A13)
|
|
|
14
|
||||
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 1
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.
|
A
|
B
|
C
|
D
|
|
|
1
|
Value
|
Rank
|
||
|
2
|
30
|
1
|
=RANK(A2,A2:A5)
|
|
|
3
|
20
|
2
|
=RANK(A3,A2:A5)
|
|
|
4
|
20
|
2
|
=RANK(A4,A2:A5)
|
|
|
5
|
10
|
4
|
=RANK(A5,A2:A5)
|
|
|
6
|
Example 2
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.
|
A
|
B
|
C
|
D
|
E
|
|
|
8
|
Athlete
|
Time
|
Race Position
|
||
|
9
|
Nathan
|
1:30
|
4
|
=RANK(B9,B9:B14,1)
|
|
|
10
|
Willie
|
1:45
|
6
|
=RANK(B9,B9:B14,1)
|
|
|
11
|
Ryan
|
1:02
|
1
|
=RANK(B9,B9:B14,1)
|
|
|
12
|
Jordan
|
1:36
|
5
|
=RANK(B9,B9:B14,1)
|
|
|
13
|
Alma
|
1:27
|
3
|
=RANK(B9,B9:B14,1)
|
|
|
14
|
Robin
|
1:03
|
2
|
=RANK(B9,B9:B14,1)
|
|
|
15
|
|||||
No comments:
Post a Comment