Rank using Multiple Columns

[Hliboi]

I'm facing a issue with ranking in Excel particularly in regards to tie breaking.
I tried several options but i guess they don't fit my issue. Its quite simple really, I'll explain:
The demo data:

row1	a	b	c	d	e	f	g	h	i	j
row2	1	2	3	4	5	6	7	8	9	10
row3
row4	87	83	74	95	69	90	73	0	74	85
row5	121	121	96	121	121	121	121	83	121	121

<tbody>
</tbody>

As you can see its easy for me to rank the first line (I'm working in columns instead of rows for the data). When i do a Rank Function gives the following result:

	k	l	m	n	o	p	q	r	s	t
row4	3	5	6	1	9	2	8	10	6	4
row5	1	1	9	1	1	1	1	10	1	1

<tbody>
</tbody>

Which is correct. The problem arises in the second line (row 5). There are ties because all of them reach the maximum of 121.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">
</code>What i would like to do is take the second row (row 2)as a tie breaker- It could also be alphabetic by replacing the numbers with letters:

row2

a

b

c

d

e

f

g

h

i

j

<tbody>
</tbody>

So even if there is a tie in row 5 it could untie or break the tie giving precedente to the number or letter in row 2. Thus row 2 will eliminate all possible ties giving the the following ranking to row 5:

row5

1

2

9

3

4

5

6

10

7

9

<tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">

</code>How could one achieve this result?


Thank You very much in advance.

 

[DRSteele]

I think one simple way to achieve your objective is to add or (subtract in your case) a fractional amount to each figure in your Row5, thereby altering the ties so that they are no longer identical. Then you would rank all the new figures.

The trick in this approach is to pick a fraction that is not an entire significant digit greater or lesser than the figure. To clarify, if you had a collection of 121s and you subtracted 10% of the figures in Row 2, you'd get a collection of 120s plus varying amounts. A problem arises when you get to the 10th item, where you'd end up subtraction 10% of 10 = 1 from the figure, giving you 120 and there might be other 120s that weren't tied prior to the amendment. So pick a number where that won't happen, like 1%; if you have less than 100 figures, chose 100 as the multiplier-divider, whichever may be the case. You can keep on using other multipliers-dividers for additional levels of tie-breaking, like you would need to in sports standings where Points ties are broken by using Goal Differential, and then by Home Wins, then by Total Goals, then by.... You get the point.

Copy the B formulas right-wards.

Vis:

	A	B	C	D	E	F	G	H	I	J	K
1	tie-breaker	1	2	3	4	5	6	7	8	9	10
2	multiplier-divider	1%
3
4	height	87	83	74	95	69	90	73	0	74	85
5	width	121	121	96	121	121	121	121	83	121	121
6
7	width rank (ties)	3	5	6	1	9	2	8	10	6	4
8	height rank (ties)	1	1	9	1	1	1	1	10	1	1
9
10	width (appended)	86.99	82.98	73.97	94.96	68.95	89.94	72.93	-0.08	73.91	84.90
11	height rank (appended)	120.99	120.98	95.97	120.96	120.95	120.94	120.93	82.92	120.91	120.90
12
13	width rank (tie-broken)	3	5	6	1	9	2	8	10	7	4
14	height rank (tie-broken)	1	2	9	3	4	5	6	10	7	8

<tbody>
</tbody>

Sheet20​

Worksheet Formulas Cell Formula B7 =RANK.EQ(B4,$B$4:$K$4) B8 =RANK.EQ(B5,$B$5:$K$5) B10 =B4-B$1*$B$2 B11 =B5-B$1*$B$2 B13 =RANK.EQ(B10,$B10:$K10) B14 =RANK.EQ(B11,$B11:$K11) <tbody> </tbody>

<tbody>
</tbody>

 

[Hliboi]

Thank you so much! It worked great! That's also a very cool lesson I learned!
Thanks again and all the best!

 

[DRSteele]

That's terrific! You're most welcome.