Breaking a tie - Excel 2003

[Robert]

Okay.....I have a ranked column of numbers from which there might be a tie.
In order to break the tie I have three tie-breaking conditions (tie-breaker
#1, tie-breaker #2, and tie-breaker #3). The rank is from lowest to highest
(smaller is better) BUT the tie-breaking conditions are from highest to
lowest (a larger number is better). I'd like to find a way, without using
VB, to create a "tie breaking rank" that applies the conditions of the three
tie-breakers in order to rank the original range of values; the tied ones and
the unique ones.

Below is a sample set of values:

Total Rank TB1 TB2 TB3 My Wish
170 10 0 2 0 10
122 7 3 2 1 7
96 6 1 0 1 6
72 3 3 2 2 3
14 1 7 10 1 1
78 4 3 3 4 4
69 2 4 1 4 2
78 4 1 1 4 5
128 9 1 1 1 9
122 7 3 0 3 8


Also, I do not want to base the new ranking on the original ranking as I
have some formatting and such within those cells that I do not want to
change. So the new ranking needs to be based on the "total" and the three
tie breaking conditions. I included the "my wish" column for what I hope the
new formula does for me.....

Hope someone can help!

Robert.

 

[Pete_UK]

You could make a new total by adding those tie-breakers to the current
total. As they operate highest to lowest, you should subtract them
from the maximum within the range, i.e.:

=A2 + (MAX(C$2:C$11)-C2) + (MAX(D$2$11)-D2) + (MAX(E$2:E$11)-E2)

Copy this down a helper column, then apply your rank formula to this.
If your tie-breakers have different weights, then you can multiply
them by some weighting factor, eg:

=1000*A2 + 100*(MAX(C$2:C$11)-C2) + 10*(MAX(D$2$11)-D2) + (MAX(E$2:E
$11)-E2)

Hope this helps.

Pete

 

[Robert]

Well.....each of the tie-breakers is weighted the same...no problem there.
However, I want to use tie-breaker #1 ONLY to break the initial tie. Then,
if there is still a tie then I wish to employ tie-breaker #2...similar for
the use of #3. The formula suggested would, mathematically, make it possible
for two of the values to still be the same; as happened when utilized for the
current data shown. The individual tie-breakers shown across each row
should, ideally, be used only for the reference total....otherwise there
might be some cross contamination of the final results. Certainly I can do
this procedure manually but I'd really love to have a built-in function to do
the same; show my new rank vs. my original rank.

Really, I wish to break the tie for the tied numbers only....the others can
simply be moved into the new ranking column. The final visual order is not
as important as the correctness of the numerical rank.

Keep up the good suggestions!

Robert.

 

[Pete_UK]

Well, this worked for me. With your data in A1:F11, put this in G2:

=IF(COUNTIF(A$2:A$11,A2)>1,(1000*A2+100*(MAX(C$2:C$11)-C2)+10*(MAX(D
$2$11)-D2)+(MAX(E$2:E$11)-E2))/1000,A2)

and this in H2:

=RANK(G2,G$2:G$11,1)

It gave the same ranks as your wish column.

Hope this helps.

Pete

 

[Robert]

Pete,

Looks like this might work. I originally used an unweighted count...so the
weighted version seems to do what I need. I have 30 total rows of data to
rank this way so I do appreciate the assistance.

robert.

 

[Pete_UK]

You're welcome, Robert - thanks for feeding back.

Pete