Ranking By Group instead of full competition

Good morning

I have a competition scoring system that currently ranks 20 teams in order, and is working fine.

I want to be able to break the 20 teams into 4 different pools, and rank within each pool instead of right across the whole 20 teams.

Please see attached file. I have had a go at it but am encountering some errors and cannot identify where i have gone wrong.

Any assistance would be greatly appreciated.

The outcome I am seeking is being able to rank the 5 teams in each pool (A - D) from 1st to 5th.

I have the results coming into columns A - F. I can get pool A to work for me no problem, however as soon as I try to step down to the next group of 5 teams I run into trouble. I assume it has something to do with my row referencing?

Thanks in advance

Darren

The ranks in column in column H seem correct.

What am I missing?

How I would do it without all the arrays:

1) Change the values in column a to just A, B, C, D - remove the numbers.

2) Put this formula in the HELPER column to "index" each of the groups. In F2, then copy down:
=A2&(1+COUNTIFS(A:A, A2,E:E, ">"&E2))

3) In column H, just put the numbers 1-5 in each group, no formula.

4) In I2 put this formula, then copy down and across the table:
=INDEX(B:B, MATCH($A2&$H2,$F:$F, 0))


Currently you only have complete data on the first group, so that's the only group that shows a true meaningful ranking. The others will improve when the data improves.

http://screencast.com/t/1s3crOqiTFNU

Hi Jerry

Your code seems to work perfectly on the points % column, however if the competition points are not aligned to this then there is a problem.

I have attached a sheet which also includes the pools results tables, and altered one of the games in Pool A to mean that the Sharks lost a match, but only by a narrow margin.
This would mean that they should drop to 2nd place due to less competition points, however they remain in first as it is only looking at the points % to determine the ranking.

any idea how i can incorporate the ranking system to look at Competition points first, and then if the same comp points it moves to points % ??

I believe that is why I went to an array system initially, however after your reply i am sure there would be an easier way.

Appreciate your help so far

Darren

ok thanks I was trying to work out why it would not open as it was opening on my end.

Simplest solution I can think of is to use this in E2, copied down / edited as needed:

=C2+I36

Thanks Jerry

I combined that with then subtracting the comp points from the points % figure in the main table and it worked perfectly (simply because the points % is the second criteria for countback, so the combined figure made it confusing)

Thanks for all of your help.

Darren