The RANK() function in Excel is limited to a single range and if you do not have a set of unique numbers to use the rank function on it will return repeating values.
There is a workaround however. If you can afford an extra column for a ‘rank total’ then it will be easy to accommodate. Or you can use an array formula.
The easiest example in creating a rank total column is to look at standings in sports:
|
Team
|
W
|
Points
|
GF
|
GA
|
Goal Differential
|
|
Team A
|
5
|
15
|
20
|
5
|
15
|
|
Team B
|
4
|
12
|
19
|
18
|
1
|
|
Team C
|
6
|
12
|
12
|
7
|
5
|
|
Team D
|
4
|
11
|
17
|
15
|
2
|
In this scenario I’m going to say the rank order will first be by points, the first tiebreaker will be wins, followed by goal differential. My rank total formula will be as follows: Points + wins/100 + goal differential/10,000. I’ve broken out how the values look and after totaling them:
|
Team
|
W
|
Points
|
GF
|
GA
|
Goal Differential
|
Win Value
|
Differential Value
|
Rank Total
|
|
Team A
|
5
|
15
|
20
|
5
|
15
|
0.05
|
0.0015
|
15.0515
|
|
Team B
|
4
|
12
|
19
|
18
|
1
|
0.04
|
0.0001
|
12.0401
|
|
Team C
|
4
|
12
|
12
|
7
|
5
|
0.04
|
0.0005
|
12.0405
|
|
Team D
|
4
|
11
|
17
|
15
|
2
|
0.04
|
0.0002
|
11.0402
|
For Team A, their rank total is made up of 15 (points), .05 (wins) and .0015 for goal differential. If the factor for goal differential was only 1,000, then goal differential adds 0.015 and now it affects the decimal position for wins and has the same effect as a sixth win, which is wrong. So you want to choose your factors carefully so as not to effect the higher ranking tiebreaker. If goal differential was only ever single digits then you could have used a denominator of 1,000 instead of 10,000.
The result of this rank total tells me Team C should be ranked higher than Team B because both teams have the same points, same wins, but Team C has the higher goal differential.
Now what you can do to pull the ranks is use the following formula:
RANK(ranktotalvalue, ranktotalcolumn)
Or if you want to put the name of the teams in order of their rank rather than just saying Team A is in position 1, then you can use the index and match functions as follows. Assume the Team column is column A and the rank total is column I:
=INDEX(A:A, MATCH(LARGE(I:I,ROW(A1)),I:I,0))
Let’s break down this formula:
=INDEX(A:A
This tells the formula I want to extract the value from column A.
LARGE(I:I,ROW(A1))
This extracts the largest value in column I. The reason I use ROW(A1) instead of the number one is because now if I drag this formula down the relative reference will become ROW(A2), ROW(A3), and ROW(A4) which then looks for the second, third, and fourth largest values respectively.
MATCH(LARGE(I:I,ROW(A1)),I:I,0)
This formula looks for where the value matches the result of the large formula calculation. Where that match is made, the related value from column A is returned. And the following list is generated:
Team A
Team C
Team B
Team D
This correctly puts Team C ahead of Team B in the rankings.
WHAT IF I DON’T HAVE ANY TIEBREAKERS?
If you do not have any tiebreakers then what you can do is pull them in the order that they appear. If you want them to be in ascending or descending order, then you will first need to sort the data in such a way.
In this case, you can calculate your rank total using a value for the row the values are on. The formula for the ‘row value’ would be calculated as follows: 1/(ROW()*100). The fraction is used to make sure the rows higher up will appear first. I multiple the denominator by 100 to push it further down the decimal location. Below is how my rank totals now look:
|
Team
|
W
|
Points
|
GF
|
GA
|
Goal Differential
|
Row Value
|
Rank Total
|
|
Team A
|
5
|
10
|
20
|
5
|
15
|
0.005
|
10.005
|
|
Team B
|
4
|
12
|
19
|
18
|
1
|
0.003333333
|
12.00333333
|
|
Team C
|
4
|
12
|
12
|
7
|
5
|
0.0025
|
12.0025
|
|
Team D
|
4
|
11
|
17
|
15
|
2
|
0.002
|
11.002
|
I changed Team A’s point total to 10 for the sake of this example. Now the top two ranked teams (B and C) both have 12 points. Because B is in a higher row and thus shows up before C, it has a higher row value which in turn gives it a higher total rank value. So the correct order now is Team B, Team C, Team D, and Team A.
THE FORMULA METHOD
Now if you don’t have the luxury to put an extra column in your worksheet, you can certainly do this in a formula, although it won’t be pretty. Essentially you’ll recalculate the rank total and search through the values using an array formula.
To recalculate the rank for the non-tiebreaker method:
{=INDEX($A$2:$A$5,MATCH(LARGE(($C$2:$C$5)+(1/(ROW($G$2:$G$5)*100)),ROW(A1)),$C$2:$C$5+(1/(ROW($G$2:$G$5)*100)),0),1)}
The INDEX formula again looks at the Team column while looking for the largest value when adding the points value to the row value. The calculation for the row value is the same as above just now dumped into a formula. An array formula has to be used to ensure each team’s results are looked at individually.
For the multiple tiebreaker scenario from above, the formula will be longer to accommodate for all the extra tiebreakers it has to look at:
{=INDEX($A$2:$A$5,MATCH(LARGE(($C$2:$C$5)+($B$2:$B$5/100)+($F$2:$F$5/10000),ROW(A1)),(($C$2:$C$5)+($B$2:$B$5/100)+($F$2:$F$5/10000)),0),1)}
Again, same logic and formulas are involved except without a rank total column it has to be done in an array. The results yield the same order as through adding an extra column.
