Rank w/ Multiple Tie Breakers

[rsieger]

Hi, I saw similar threads relating to Ranking w/ a single tie-breaker, but I'm getting tripped up on a formula that requires multiple tie-breakers.

<TABLE style="WIDTH: 178pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=236 border=0><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 1592" width=56><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 1649" width=58><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 1991" width=70><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1479" width=52><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl83 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 86pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=114 colSpan=2 height=17>A_______B_______ </TD><TD class=xl78 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=70>C_________ </TD><TD class=xl78 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=52>D______</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Driver</TD><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BI</TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">COM</TD><TD class=xl80 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">COL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1 </TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Paul</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>George</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ringo</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2</TD></TR></TBODY></TABLE>​

The Ranking criteria are as follows:
Highest # in column "B", if a tie, highest # in column "C", if tie, highest # in column "D". If there is still a tie, the Rank would be the order that the data appears on the list.

The Result should be as follows:
Rank Driver
1 Paul
2 George
3 Ringo
4 John

Can someone please show me a formula that produces the above result? Thanks in advance for your help!

 

[pgc01]

Hi Tony

Thank you.

You are absolutely right, the first "IF(COUNTIF(E:E,E2)=1, E2," was for the case of using E:E as an auxillary column,that I may have used for testing. You can simply erase it.

=RANK($B2,$B$2:$B$15) + SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15>$C2))+SUMPRODUCT(($B$2:$B$15=$B2)*($C$2:$C$15=$C2)*($D$2:$D$15>$D2))+SUMPRODUCT(($B$1:$B1=$B2)*($C$1:$C1=$C2)*($D$1:$D1=$D2))

 

[toeknee75]

PGC01...

OK... So it worked in one instance, and not in another... am I doing something wrong? i used the formula in another sheet with all the values filled in and it worked fine. But it's not working correctly here.

Here's the code I have in R66 copied down.
=RANK($F66,$F$66:$F$87) + SUMPRODUCT(($F$66:$F$87=F66)*($H$66:$H$87>$H66))+SUMPRODUCT(($F$66:$F$87=$F66)*($H$66:$H$87=$H66)*($J$66:$J$87>$J66))+SUMPRODUCT(($F$65:$F65=$F66)*($H$65:$H65=$H66)*($J$65:$J65=$J66))

Even with all of the fields currently at "0.00" Shouldn't it at least start at "1" in the ranking column?

Here's the same formula with a similar problem.

This formula is in R37:
=RANK($F37,$F$37:$F$58) + SUMPRODUCT(($F$37:$F$58=F37)*($H$37:$H$58>$H37))+SUMPRODUCT(($F$37:$F$58=$F37)*($H$37:$H$58=$H37)*($J$37:$J$58>$J37))+SUMPRODUCT(($F$36:$F36=$F37)*($H$36:$H36=$H37)*($J$36:$J36=$J37))

Shouldn't the next cell start with "2" because of all "ties" with a value of 0?


Thank you for your help.

-Tony

 

[pgc01]

Hi Tony

If you analyse the formula you'll notice that the last part uses the header of the columns, assuming the headers have text.

There's your problem, usually all tables have text in the header row, but you have merged cells in the headers and so their value is zero, which messes the formulas.

If you don't merge the cells and have real text in the headers, as the tables usually do, I think the formulas will work ok. Please try.


P. S. Avoid posting pictures, if anyone wants to perform a test with your data the pictures are useless and no one will introduce data manually. Use a html maker like, 2 examples:

- http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

- send a PM to RichardSchollar and he'll tell you how to use his

As last ressort post data as text. Anything that can be worked with.

 

[toeknee75]

Thank You PGC1 , that worked.
and I'm sorry about posting screenshots, I downlaoded the prog from that link and will do that from now on.

Thanks Again!
-Tony

 

[pgc01]

I'm glad it helped.

Remark:

If you want you can make the formula independent of the nature of the headers, you just have to subtract the result of the first comparison:

In R66:

=RANK(F66,$F$66:$F$87) + SUMPRODUCT(($F$66:$F$87=F66)*($H$66:$H$87>H66))+SUMPRODUCT(($F$66:$F$87=F66)*($H$66:$H$87=H66)*($J$66:$J$87>J66))+SUMPRODUCT(($F$65:F65=F66)*($H$65:H65=H66)*($J$65:J65=J66))-($F$65=F66)*($H$65=H66)*($J$65=J66)

This makes the formula bigger, but it's independent from the headers.

 

[clark402]

I'm trying to apply the concepts in these earlier posts to my spreadsheet formulas.

I have a list of 20 kids in column A with test scores in column CC. I need to rank the test scores high to low without sorting, and then break the ties multiple times without adding decimals or changing the scores.
The 1st tie break is done on the total amount of bonus questions the kids answered correctly which are entered in column CG. If there are still ties then the tie will be broken by the result of a random question. Either the kid gets the random question correct indicated with a "Y" for yes, or they get it wrong as indicated by an "N" in column CH. If there are still ties we will use a sudden-death method until there is a clear winner which will also be indicated with a Y or N entered in the field in column CI. Column CJ is for the ranking. Column DX is how I ranked them manually which is what I want Excel to do for me correctly in column CJ.

As you can see Taryn, Corbin, & Dallee, in rows 5, 16, & 17 respectively, all scored 27 on their test which left them all tied for 5th place. They also tied on the bonus questions by all of them getting 3 of the 5 bonus questions correct which left them tied for 5th place. So we went to the random questions. Corbin got his random question correct, but Taryn & Dallee didn't get their's so Corbin is awarded 5th place, but Taryn and Dallee are still tied for 6th place. We went to a sudden-death round of questions and Dallee won the sudden-death which should let Dallee keep the 6th place and push Taryn to 7th place.

This is how I wrote my formula for CJ17:

=RANK(CC17,$CC$2:$CC$21)+SUMPRODUCT(($CC$2:$CC$21=CC17)*($CG$2:$CG$21>CG17))+SUMPRODUCT(($CC$2:$CC$21=CC17)*($CG$2:$CG$21=CG17)*($CH$2:$CH$21>CH17))+SUMPRODUCT(($CC$2:$CC$21=CC17)*($CG$2:$CG$21=CG17)*($CH$2:$CH$21=CH17)+SUMPRODUCT(($CC$2:$CC$21=CC17)*($CG$2:$CG$21=CG17)*($CH$2:$CH$21=CH17)*($CI$2:$CI$21=CI17))-($CC$1=CC17)*($CG$1=CG17)*($CH$1=CH17)*($CI$1=CI17)-1)

I have tried to follow the instruction for posting the miniature screen shot of my spreadsheet, but it doesn't look like it's working too well so I recreated it with a table.

My problem is that Excel shows my 6th place kid tied for 8th place (see CJ17 & DX17), so obviously I have a problem in my formula. All other placings look fine. (If the kids got a 0 on the test score Excel ranked them all in 20th place which is acceptable to me.) Can someone please help me fix my formula?

Thank you in advance.

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]CC[/TD]
[TD="align: center"]CG[/TD]
[TD="align: center"]CH[/TD]
[TD="align: center"]CI[/TD]
[TD="align: center"]CJ[/TD]
[TD="align: center"]DX[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]NAME
[/TD]
[TD="align: center"]TEST SCORE[/TD]
[TD="align: center"]TOTAL # BONUS QUESTIONS[/TD]
[TD="align: center"]RANDOM BONUS QUESTION[/TD]
[TD="align: center"]SUDDEN-DEATH QUESTIONS[/TD]
[TD="align: center"]EXCEL RANK[/TD]
[TD="align: center"]My MANUAL RANKS[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Hunter[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Dustee[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Hadlee[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Taryn[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Tucker[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Anna[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Charly[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]HadleeK[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Charley[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Carson[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Austyn[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Arianna[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Paycie[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Miki[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Corbin[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Dallee[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Piper[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Ellie[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Destiny[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]Braxton[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]14[/TD]
[/TR]
</tbody>[/TABLE]