I am trying to figure out how to use another column as a tiebreaker when
using the rank feature. For example:



Column A Column B Rank

150 55 3

362 65 5

251 67 6

119 55 3

167 87 8

225 44 1

269 83 7

198 44 1



I want to rank Column B but if there is a tie, I want to look at column A to
break that tie.



Can you help me?

=1+SUMPRODUCT(--($B$1:$B$8<B1))+SUMPRODUCT(--($A$1:$A$8<A1),--($B$1:$B$8=B1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mbass" wrote in message
...
I am trying to figure out how to use another column as a tiebreaker when
using the rank feature. For example:



Column A Column B Rank

150 55 3

362 65 5

251 67 6

119 55 3

167 87 8

225 44 1

269 83 7

198 44 1



I want to rank Column B but if there is a tie, I want to look at column A
to
break that tie.



Can you help me?

Before I get hauled over the coals

=1+COUNTIF($B$1:$B$8,"<"&B1)+SUMPRODUCT(--($A$1:$A$8<A1),--($B$1:$B$8=B1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"mbass" wrote in message
...
I am trying to figure out how to use another column as a tiebreaker when
using the rank feature. For example:



Column A Column B Rank

150 55 3

362 65 5

251 67 6

119 55 3

167 87 8

225 44 1

269 83 7

198 44 1



I want to rank Column B but if there is a tie, I want to look at column A
to
break that tie.



Can you help me?

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

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