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