Rank Duplicates Based on Other Cell Value - Part 4

[Drogan]

Ok, one last problem...I hope.

So I have formulas to indicate how duplicate scores should be ranked. Everything works perfectly except for one thing: multiple scores are not ranked properly if given near exact highs and lows as shown here:

Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"> <col style="width: 75px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">KC</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">BF</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">NS</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">RM</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">JP</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">RC</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">SW</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">DA</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">PF</td> <td style="font-weight: bold; background-color: rgb(255, 255, 0);">SC</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>
</td> <td style="text-align: right;">9.2</td> <td style="text-align: right;">9</td> <td>
</td> <td style="text-align: right;">8</td> <td>
</td> <td style="text-align: right;">9.2</td> <td>
</td> <td style="text-align: right;">9.3</td> <td style="text-align: right;">8.9</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>
</td> <td style="text-align: right;">9.5</td> <td style="text-align: right;">9.4</td> <td>
</td> <td style="text-align: right;">8.8</td> <td>
</td> <td style="text-align: right;">9.3</td> <td>
</td> <td style="text-align: right;">9.4</td> <td style="text-align: right;">9</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>
</td> <td style="text-align: right;">9.5</td> <td style="text-align: right;">9.5</td> <td>
</td> <td style="text-align: right;">8.9</td> <td>
</td> <td style="text-align: right;">9.6</td> <td>
</td> <td style="text-align: right;">9.4</td> <td style="text-align: right;">9.2</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>
</td> <td style="text-align: right;">9.6</td> <td style="text-align: right;">9.7</td> <td>
</td> <td style="text-align: right;">9</td> <td>
</td> <td style="text-align: right;">9.7</td> <td>
</td> <td style="text-align: right;">9.5</td> <td style="text-align: right;">9.5</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>
</td> <td style="text-align: right;">9.8</td> <td style="text-align: right;">9.8</td> <td>
</td> <td style="text-align: right;">9.4</td> <td>
</td> <td style="text-align: right;">9.7</td> <td>
</td> <td style="text-align: right;">10</td> <td style="text-align: right;">9.6</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">0</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">28.6</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">28.6</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">0</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">26.7</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">0</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">28.6</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">0</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">28.3</td> <td style="font-weight: bold; background-color: rgb(0, 255, 0); text-align: right;">26.7</td></tr></tbody></table>
Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 70px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold; font-size: 9pt;">R2 Clean</td> <td style="font-weight: bold; text-align: right;">28.6</td> <td style="font-weight: bold; text-align: right;">28.6</td> <td style="font-weight: bold; text-align: right;">28.6</td> <td style="font-weight: bold; text-align: right;">28.3</td> <td style="font-weight: bold; text-align: right;">26.7</td> <td style="font-weight: bold; text-align: right;">26.7</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold; font-size: 9pt;">Names</td> <td style="font-weight: bold;">SW</td> <td style="font-weight: bold;">BF</td> <td style="font-weight: bold;">NS</td> <td style="font-weight: bold;">PF</td> <td style="font-weight: bold;">SC</td> <td style="font-weight: bold;">JP</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td></tr></tbody></table>
Notice how BF has 9.8 high and 9.2 low -- then NS has 9.8 high and 9 low -- then SW has 9.7 high and 9.2 low. So with that, SW should be 3rd, not 1st as shown above. So it should be: BF (9.8 / 9.2), NS (9.8 / 9), SW (9.7 / 9.2). How can I edit my formula to properly reflect this? Here is the formula from Sheet 2, Cell B7 (copied across accordingly):

<table style="font-size: 10pt; border-color: rgb(0, 255, 0); color: rgb(0, 0, 0); border-style: groove; font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody><tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B7</td> <td>{=IF(AND(ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J)))),ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J)))),ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))))),"",IF(AND(ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J)))),ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))))),INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))),IF(AND(ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J)))),ISERROR(INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))))),INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))),INDEX(Sheet1!$C$1:$L$1,SUMPRODUCT(--(Sheet1!$C$17:$L$17=B6),--(Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14=(LARGE(IF(Sheet1!$C$17:$L$17=B6,Sheet1!$C$10:$L$10*Sheet1!$C$13:$L$13*Sheet1!$C$14:$L$14),COUNTIF($B$6:B$6,B6)))),COLUMN($A:$J))))))}</td></tr> </tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Any help in properly ranking these multiple duplicate scores would be greatly appreciated. Thank you.

Excel tables to the web >> Excel Jeanie HTML 4

 

[AlphaFrog]

I don't follow exactly what you are doing but here's how you can break ties.

You add an insignificant amount to each score based on your tie breaking criteria. This insignificant amount is too small to change a rank for non-tied values, but it is enough to differentiate (break ties) values that would normally be equal.

Example:
If the tie break criteria is Max/Min for each column, then add a value like this to each of the scores in row 17

Score + (Max/Min)/1000

Again, the addition of the tie breaking criteria is not enough to change a rank for non-ties, but it will be enough to change the values for ties so they are not exactly equal. Then you can use a LARGE or a RANK function and not worry about ties.

 

[Drogan]

Hello, AlphaFrog

Thanks for the suggestion, but unfortunately it's still not giving me exactly what I need. After adding MAX/MIN/1000, it's still slightly out of order. So if I were to add it to the 3 ties, then it would make it NS, BF, SW instead of BF, NS, SW. The problem is that the "higher" low score, in this case 9.8/9.2/1000, results in a lower number than the "lower" low score, in this case 9.8/9/1000. Though it was close since it did put SW last at least. Hopefully a better solution can be found. Thanks again.

 

[Drogan]

Hello again, AlphaFrog

So after playing around some more with your suggestion, I found a solution that worked. I just took MAX/1000000000 which works perfectly. Thanks again!

 

[AlphaFrog]

Glad you were able to work it out and thanks for the feedback.