# How do I Rank Data into two groups

#### shudra

##### New Member
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:

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

Thanks
Sam

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Assuming the list will always be sorted in order (best to worst):

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.

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?

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.

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

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

Replies
3
Views
267
Replies
3
Views
172
Replies
5
Views
256
Replies
4
Views
1K
Replies
8
Views
219

1,203,072
Messages
6,053,377
Members
444,659
Latest member
vuphihung

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

### Which adblocker are you using?

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

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