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; (306140.63, « »)

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?

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