Unique Rank and Tie Breaker

[DRidell]

Hello all.

I have found this forum to be very helpful in the past, always finding the answer that I need.

I have found a way (By searching this forum) of assigning a unique rank, however I need to take it one step further.

In the example below, Tom and Sally have an equal amount of points, however Tom shoud be ranked higher based on number of wins.

I know that I could do a custom sort on points and then by wins, but I was hoping for a formula solution.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Rank</TD><TD style="TEXT-ALIGN: center">Name</TD><TD style="TEXT-ALIGN: center">Points</TD><TD style="TEXT-ALIGN: center">Wins</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">1</TD><TD style="TEXT-ALIGN: center">Joe</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">2</TD><TD style="TEXT-ALIGN: center">Jim</TD><TD style="TEXT-ALIGN: center">400</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Sally</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">300</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Tom</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">300</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">5</TD><TD style="TEXT-ALIGN: center">Mark</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center" colSpan=4>Expected Results</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">Rank</TD><TD style="TEXT-ALIGN: center">Name</TD><TD style="TEXT-ALIGN: center">Points</TD><TD style="TEXT-ALIGN: center">Wins</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">1</TD><TD style="TEXT-ALIGN: center">Joe</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">2</TD><TD style="TEXT-ALIGN: center">Jim</TD><TD style="TEXT-ALIGN: center">400</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Tom</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">300</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Sally</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">300</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Tahoma; FONT-SIZE: 9pt">5</TD><TD style="TEXT-ALIGN: center">Mark</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=RANK(C2,$C$2:$C$6,0)+COUNTIF($C$2:C2,C2)-1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4







Thank you in advance for any help.

Don

 

[barry houdini]

Hello Don,

You could use this version in row 2 copied down

=RANK(C2,C$2:C$6)+SUMPRODUCT((C$2:C$6=C2)*(D2<?XML:NAMESPACE PREFIX = D$2 /><D$2:D$6))< p>< D$2:D$6))</D$2:D$6))<>
<D$2:D$6))< p>
but if points and wins are both the same that will give the same rank so to give unique ranks even when points and wins are the same change to this

=RANK(C2,C$2:C$6)+SUMPRODUCT((C$2:C$6=C2)*(D2< D$2:D$6))+SUMPRODUCT((C$2:C2=C2)*(D2=D$2:D2))-1<D$2:D$6))+SUMPRODUCT((C$2:C2=C2)*(D2=D$2:D2))-1< p></D$2:D$6))+SUMPRODUCT((C$2:C2=C2)*(D2=D$2:D2))-1<>
</D$2:D$6))<>

 

[jimrward]

i would add another column called tiebreaker and set the value to be =points + (wins /10) or =points + (wins /100) to get a decimal value and rank on that using your original RANK formula =RANK(C2,$C$2:$C$6,0)+COUNTIF($C$2:C2,C2)-1 but change the column references to E where your new tiebreaker data lives

 

[DRidell]

Thank you both very much.

Barry that is exactly what I needed, Thank you.


Don

 

[jmanley]

Hello Don,

You could use this version in row 2 copied down

=RANK(C2,C$2:C$6)+SUMPRODUCT((C$2:C$6=C2)*(D2<!--?XML:NAMESPACE PREFIX = D$2 /--><d$2:d$6))< p="">< D$2:D$6))</d$2:d$6))<>
<d$2:d$6))< p="">
but if points and wins are both the same that will give the same rank so to give unique ranks even when points and wins are the same change to this

=RANK(C2,C$2:C$6)+SUMPRODUCT((C$2:C$6=C2)*(D2< D$2:D$6))+SUMPRODUCT((C$2:C2=C2)*(D2=D$2:D2))-1<d$2:d$6))+sumproduct((c$2:c2=c2)*(d2=d$2:d2))-1< p=""></d$2:d$6))+sumproduct((c$2:c2=c2)*(d2=d$2:d2))-1<>
</d$2:d$6))<>

Wow, works beautifully.

It seems like there is a SUMPRODUCT happening within array1 of the SUMPRODUCT. I thought that normally the function is designed to sum the products of the corresponding members of array1 & array2. How did you know that works?