Rank using another column for tiebreaker
This thread has been fragmented so we ( I ) can't see what you're responding
to.
Try this...
=RANK(V2,V$2:V$14)+SUMPRODUCT(--(V2=V$2:V$14),--(D2<D$2:D$14))
Copy down as needed
--
Biff
Microsoft Excel MVP
wrote in message
...
Okay, so I created a spreadsheet that tracks the NHL season
standings. I have a similar problem, but don't quite understand where
to use that formula above. In my spreadsheet, rank is determined by
points, but in the case of a tie, it should be further determined by
number of wins. I've used the formula:
=RANK(V2,$V$2:$V$14,0)
starting in A2 and copied down the A column to determine each teams
rank (it updates based on row number), but in the case of the teams in
rows 4 and 6, they're tied for fourth place based on points. The team
in row 6 wins the tie because they have 2 more wins (row D). How do I
adjust my RANK formula to check the D column after the initial ranking
if there is a tie? Do I paste your formula above (adusting columns
and rows) all down the A column? I know I've got it wrong... I'm
lost :-)
A D V
1 Rank Wins Points
2 1 50 106
3 2 45 99
4 4 40 89
5 3 43 94
6 4 42 89
7 6 39 88
8 7 38 86
9 8 39 84
10 9 37 82
11 10 38 81
12 11 36 80
13 12 36 78
14 13 33 77
|