How do I Rank Data into two groups

shudra

New Member
Joined
Oct 17, 2011
Messages
2
Hi

I can't think how to do this

I have some data I would like to rank. Rather than Ranking it in the usual way, I would like to rank the data as follows:
1st to last in an 'A competition' (the best athletes from each team)
and
1st to last in the 'B competition' (the second bests athlete from each team)

If you don't understand me, the bottom table here shows what I would like to achieve:

upload images

I would be so grateful if someone can help me with this.

Thanks
Sam
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Assuming the list will always be sorted in order (best to worst):

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;;">Team</td><td style="font-weight: bold;;">Athlete</td><td style="font-weight: bold;;">Long Jump</td><td style="font-weight: bold;;">A</td><td style="font-weight: bold;;">B</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">East</td><td style=";">John</td><td style="text-align: right;;">7.65</td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">East</td><td style=";">Jack</td><td style="text-align: right;;">7.54</td><td style=";"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">South</td><td style=";">Ben</td><td style="text-align: right;;">6.65</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">West</td><td style=";">Pete</td><td style="text-align: right;;">4.56</td><td style="text-align: right;;">3</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">West</td><td style=";">Dave</td><td style="text-align: right;;">4</td><td style=";"></td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">South</td><td style=";">Sam</td><td style="text-align: right;;">3.32</td><td style=";"></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Central</td><td style=";">Tom</td><td style="text-align: right;;">2.99</td><td style="text-align: right;;">4</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Central</td><td style=";">James</td><td style="text-align: right;;">2.34</td><td style=";"></td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D12</th><td style="text-align:left">=IF(<font color="Blue">$C12>=SUMIF(<font color="Red">$A$12:$A$19,$A12,$C$12:$C$19</font>)/2,1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E12</th><td style="text-align:left">=IF(<font color="Blue">$C12<=SUMIF(<font color="Red">$A$12:$A$19,$A12,$C$12:$C$19</font>)/2,1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D13</th><td style="text-align:left">=IF(<font color="Blue">$C13>=SUMIF(<font color="Red">$A$12:$A$19,$A13,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D12</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E13</th><td style="text-align:left">=IF(<font color="Blue">$C13<=SUMIF(<font color="Red">$A$12:$A$19,$A13,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E12</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D14</th><td style="text-align:left">=IF(<font color="Blue">$C14>=SUMIF(<font color="Red">$A$12:$A$19,$A14,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D13</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E14</th><td style="text-align:left">=IF(<font color="Blue">$C14<=SUMIF(<font color="Red">$A$12:$A$19,$A14,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E13</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D15</th><td style="text-align:left">=IF(<font color="Blue">$C15>=SUMIF(<font color="Red">$A$12:$A$19,$A15,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D14</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E15</th><td style="text-align:left">=IF(<font color="Blue">$C15<=SUMIF(<font color="Red">$A$12:$A$19,$A15,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E14</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D16</th><td style="text-align:left">=IF(<font color="Blue">$C16>=SUMIF(<font color="Red">$A$12:$A$19,$A16,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D15</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E16</th><td style="text-align:left">=IF(<font color="Blue">$C16<=SUMIF(<font color="Red">$A$12:$A$19,$A16,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E15</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D17</th><td style="text-align:left">=IF(<font color="Blue">$C17>=SUMIF(<font color="Red">$A$12:$A$19,$A17,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D16</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E17</th><td style="text-align:left">=IF(<font color="Blue">$C17<=SUMIF(<font color="Red">$A$12:$A$19,$A17,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E16</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D18</th><td style="text-align:left">=IF(<font color="Blue">$C18>=SUMIF(<font color="Red">$A$12:$A$19,$A18,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D17</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E18</th><td style="text-align:left">=IF(<font color="Blue">$C18<=SUMIF(<font color="Red">$A$12:$A$19,$A18,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E17</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D19</th><td style="text-align:left">=IF(<font color="Blue">$C19>=SUMIF(<font color="Red">$A$12:$A$19,$A19,$C$12:$C$19</font>)/2,MAX(<font color="Red">D$12:D18</font>)+1,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E19</th><td style="text-align:left">=IF(<font color="Blue">$C19<=SUMIF(<font color="Red">$A$12:$A$19,$A19,$C$12:$C$19</font>)/2,MAX(<font color="Red">E$12:E18</font>)+1,""</font>)</td></tr></tbody></table></td></tr></table><br />

If the list needs to be sorted otherwise, that's a tough nut to crack. RANK() doesn't work with Array Formulas.

This also will only work if two members from the same team aren't tied (if they are, you'll get the same guy in both lists), and assumes that there will always be only 2 people from each team.
 
Upvote 0
Agreed, RANK() doesn't work with array formulas, and if used with as an array formula simply numbers positions as it would before, and gives N/A for those that don't match the criteria.

I would assume that in 2010 the RANK.EQ has the same problem?
 
Upvote 0
I was using an array formula to drop down to the relevant results:
=IF($C12>=SUMIF($A$12:$A$19,$C$12:$C$19,$C$12:$C$19)/2,$C$12:$C$19)

That got me the proper array (assuming no ties and only 2 people per team), but I couldn't find a way to rank it effectively because of the issues with RANK().

Hrmph.
 
Upvote 0
Also assuming the list is sorted and just 2 names per team.

Excel Workbook
ABCDE
11TeamAthleteLong JumpAB
12EastJohn7.651 
13EastJack7.541
14SouthBen6.652
15WestPete4.563
16WestDave42
17SouthSam3.323
18CentralTom2.994
19CentralJames2.344
Rank
 
Upvote 0
If the list is sorted but there can be more than two names per team, then just a minor change to the column E formula only.

Excel Workbook
ABCDE
11TeamAthleteLong JumpAB
12EastJohn7.651 
13EastJack7.541
14SouthBen6.652
15WestPete4.563
16EastTom4.3
17WestDave42
18SouthSam3.323
19CentralTom2.994
20SouthFohn2.5
21CentralJames2.344
Rank
 
Upvote 0

Forum statistics

Threads
1,226,723
Messages
6,192,680
Members
453,745
Latest member
fueled_vba

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