### Tags: column, example, excel, feature, figure, microsoft, msdn, rank, software, tiebreaker

# Rank using another column for tiebreaker

On Microsoft » Microsoft Excel

4,761 words with 4 Comments; publish: Fri, 23 May 2008 17:53:00 GMT; (306109.38, « »)

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?

*http://excel.todaysummary.com/q_microsoft-excel_375909.html*

All Comments

Leave a comment...

- 4 Comments
- =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" <mbass.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:CF027313-B52C-4F34-B2BB-23BD8DEBC833.excel.todaysummary.com.microsoft.com...

>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; Fri, 23 May 2008 17:54:00 GMT

- =1+SUMPRODUCT(--($B$1:$B$8<B1))+SUMPRODUCT(--($A$1:$A$8<A1),--($B$1:$B$8=B1))
- 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" <mbass.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:CF027313-B52C-4F34-B2BB-23BD8DEBC833.excel.todaysummary.com.microsoft.com...

>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?

>

#2; Fri, 23 May 2008 17:55:00 GMT

- Before I get hauled over the coals
- 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

#3; Fri, 23 May 2008 17:56:00 GMT

- Okay, so I created a spreadsheet that tracks the NHL season
- 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

<jim.mcphee.excel.todaysummary.com.cox.net> wrote in message

news:39001878-8535-44f4-a706-c22dae21d47d.excel.todaysummary.com.13g2000hsb.googlegroups.com...

> 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

#4; Fri, 23 May 2008 17:57:00 GMT

- This thread has been fragmented so we ( I ) can't see what you're responding