Rank using Multiple Columns

Hliboi

New Member
Joined
May 19, 2011
Messages
9
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:



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]row1[/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]
[/TR]
[TR]
[TD]row2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]row3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row4[/TD]
[TD]87[/TD]
[TD]83[/TD]
[TD]74[/TD]
[TD]95[/TD]
[TD]69[/TD]
[TD]90[/TD]
[TD]73[/TD]
[TD]0[/TD]
[TD]74[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]row5[/TD]
[TD]121[/TD]
[TD]121[/TD]
[TD]96[/TD]
[TD]121[/TD]
[TD]121[/TD]
[TD]121[/TD]
[TD]121[/TD]
[TD]83[/TD]
[TD]121[/TD]
[TD]121[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]row4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]row5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]row2[/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]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]row5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
<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.
 
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:
ABCDEFGHIJK
width rank (ties)
height rank (ties)
width (appended)
height rank (appended)
width rank (tie-broken)
height rank (tie-broken)

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]tie-breaker[/TD]
[TD="bgcolor: #FCE4D6, align: right"]1[/TD]
[TD="bgcolor: #FCE4D6, align: right"]2[/TD]
[TD="bgcolor: #FCE4D6, align: right"]3[/TD]
[TD="bgcolor: #FCE4D6, align: right"]4[/TD]
[TD="bgcolor: #FCE4D6, align: right"]5[/TD]
[TD="bgcolor: #FCE4D6, align: right"]6[/TD]
[TD="bgcolor: #FCE4D6, align: right"]7[/TD]
[TD="bgcolor: #FCE4D6, align: right"]8[/TD]
[TD="bgcolor: #FCE4D6, align: right"]9[/TD]
[TD="bgcolor: #FCE4D6, align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #F8CBAD"]multiplier-divider[/TD]
[TD="bgcolor: #F8CBAD, align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC"]height[/TD]
[TD="bgcolor: #FFF2CC, align: right"]87[/TD]
[TD="bgcolor: #FFF2CC, align: right"]83[/TD]
[TD="bgcolor: #FFF2CC, align: right"]74[/TD]
[TD="bgcolor: #FFF2CC, align: right"]95[/TD]
[TD="bgcolor: #FFF2CC, align: right"]69[/TD]
[TD="bgcolor: #FFF2CC, align: right"]90[/TD]
[TD="bgcolor: #FFF2CC, align: right"]73[/TD]
[TD="bgcolor: #FFF2CC, align: right"]0[/TD]
[TD="bgcolor: #FFF2CC, align: right"]74[/TD]
[TD="bgcolor: #FFF2CC, align: right"]85[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFF2CC"]width[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]96[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]83[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]
[TD="bgcolor: #FFF2CC, align: right"]121[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]8[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #C6E0B4, align: right"]86.99[/TD]
[TD="bgcolor: #C6E0B4, align: right"]82.98[/TD]
[TD="bgcolor: #C6E0B4, align: right"]73.97[/TD]
[TD="bgcolor: #C6E0B4, align: right"]94.96[/TD]
[TD="bgcolor: #C6E0B4, align: right"]68.95[/TD]
[TD="bgcolor: #C6E0B4, align: right"]89.94[/TD]
[TD="bgcolor: #C6E0B4, align: right"]72.93[/TD]
[TD="bgcolor: #C6E0B4, align: right"]-0.08[/TD]
[TD="bgcolor: #C6E0B4, align: right"]73.91[/TD]
[TD="bgcolor: #C6E0B4, align: right"]84.90[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #C6E0B4, align: right"]120.99[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.98[/TD]
[TD="bgcolor: #C6E0B4, align: right"]95.97[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.96[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.95[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.94[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.93[/TD]
[TD="bgcolor: #C6E0B4, align: right"]82.92[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.91[/TD]
[TD="bgcolor: #C6E0B4, align: right"]120.90[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #A9D08E, align: right"]3[/TD]
[TD="bgcolor: #A9D08E, align: right"]5[/TD]
[TD="bgcolor: #A9D08E, align: right"]6[/TD]
[TD="bgcolor: #A9D08E, align: right"]1[/TD]
[TD="bgcolor: #A9D08E, align: right"]9[/TD]
[TD="bgcolor: #A9D08E, align: right"]2[/TD]
[TD="bgcolor: #A9D08E, align: right"]8[/TD]
[TD="bgcolor: #A9D08E, align: right"]10[/TD]
[TD="bgcolor: #A9D08E, align: right"]7[/TD]
[TD="bgcolor: #A9D08E, align: right"]4[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #A9D08E, align: right"]1[/TD]
[TD="bgcolor: #A9D08E, align: right"]2[/TD]
[TD="bgcolor: #A9D08E, align: right"]9[/TD]
[TD="bgcolor: #A9D08E, align: right"]3[/TD]
[TD="bgcolor: #A9D08E, align: right"]4[/TD]
[TD="bgcolor: #A9D08E, align: right"]5[/TD]
[TD="bgcolor: #A9D08E, align: right"]6[/TD]
[TD="bgcolor: #A9D08E, align: right"]10[/TD]
[TD="bgcolor: #A9D08E, align: right"]7[/TD]
[TD="bgcolor: #A9D08E, align: right"]8[/TD]

</tbody>
Sheet20

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=RANK.EQ(B4,$B$4:$K$4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=RANK.EQ(B5,$B$5:$K$5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=B4-B$1*$B$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=B5-B$1*$B$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=RANK.EQ(B10,$B10:$K10)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]=RANK.EQ(B11,$B11:$K11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you so much! It worked great! That's also a very cool lesson I learned!
Thanks again and all the best!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top