Excel - Breaking a tie - Excel 2003

Asked By Rober

13-Oct-08 08:22 PM

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
(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.

14-Oct-08 08:37 PM

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.:

=3DA2 + (MAX(C$2:C$11)-C2) + (MAX(D$2:D$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:

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

Hope this helps.

Pete

e. =A0
er
ghest
ing
ree
and
ree
pe the

13-Oct-08 09:36 PM

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.

14-Oct-08 08:37 PM

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

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

and this in H2:

=3DRANK(G2,G$2:G$11,1)

It gave the same ranks as your wish column.

Hope this helps.

Pete


. =A0
hen,
r
ssible
the
n do
o do
an
not
E
a tie. =A0
reaker
o highest
to
t using
e three
ones and
0
6
3
1
4
2
5
s I
o
e three
I hope the

14-Oct-08 08:39 AM

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.

14-Oct-08 08:38 PM

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

Pete

the
to
here.. =A0
=A0Then,
r for
t possible
for the
row
ere
I can do
on to do
rs can
r is not
ent
)
s.
E$2:E
be a tie. =A0
ie-breaker
st to highest
est to
thout using
f the three
ied ones and
=A0 10
=A0 7
=A0 6
=A0 3
=A0 1
=A0 4
=A0 2
=A0 5
=A0 9
=A0 8
ng as I
nt to
d the three
hat I hope the