Find a Backgammon Winner & runner up

Hi all,
I play backgammon on a regular basis at a club which is growing rapidly. We usually have no problems working out who the winner is. But as the numbers of players are growing we run into a few problems. First off we always run out of time and never know what format to play. so I have started a workbook which allows us to determine how many teams to play and to what amount of points, it also calculates if semi finals & finals are included (Setup sheet). So next problem is once we know the correct format ( Template or Template(2) ), select the number of teams & players to display the correct format, Select the number of points to play to B5, this adds the correct drop down lists to the points table. Then select the names in column B. Then as matches completed, enter the scores in the sections below the grey section. The ones above have formulas to mirror the score for the other player.
So for those of you who don't know how to play backgammon, on a 5 point match the winner gets the 5 points, and the loser gets however many games he/she won. so for each entry you find one of the players names, enter how many games won & lost, and the table will enter the reverse score for the opponent ie. Dave played Sean, Dave W5 L3, Sean W3 L5.
Now that part is easy, now for the difficult part. as you can see there is 3 tables as we could have up to 30 players. We need to find a winner from each team and a runner up. They then go onto play a semi final and then a final. This could be just one team or 2 or 3 depending on number of players. I will worry about that section later.
What I am stuck on is determining the winner & runner up for each table.
How the winner is calculated:
If we have 1 player who has won the maximum amount of games column Y then they are the winner.
2nd place could be the player with the 2nd largest wins, but if there is more than one player with the same wins then the decision is made on points difference column AC, if they have the same points difference then decision made on points won column AA. But if they are the same (and it does happen) then the decision is made on the match score between those players
ie if Sean won with 7 matches
Dave T won 6 matches, 6 points diff, 30 points won
Dave H won 6 matches, 6 points diff, 30 points won
Dave T & Dave H had a draw, but their match Dave T won 5, Dave H won 3, therefore Dave T gets 2nd place

I have spent hours trying to work out some formulas to determine the winner and runner up and have gone slightly brain dead with it all.
I am hoping some of you guys who are far better than I with formulas can help me. I will confess I have developed more of my VBA skills than formulas, but would prefer to stick with formulas if possible.
Macros need to be enabled as it uses functions to calculate the number of wins & losses (Column Y & Z) of course if you can find a formula for this then even better.

I would prefer no named ranges as I will be copying the template each week to a new sheet and this will only cause problems.
Winner can be shown either with conditional formatting, same with runner up, or in different cells. I will leave it to you to come up with the best solution you can.
Columns AC onwards are not critical and can be changed/deleted, these were just helper columns.

If you need anymore information let me know.

Thanks in advance for any help.

Hi,

Although I don't understand the backgammon at all, I find this case is quite interesting.

First let me point something that maybe you never think about. Let say there are 3 candidates for the winner, each of them has same Points diff value (column AC) and Win Points value (column AA). You say that "the decision is made on the match score between those players". It is acceptable if there are only 2 players have the same value, so one must be the winner and the other is the loser. But how if there are more than 2 players ?

Let say there are 3 players : A, B, C.
A wins against B, but looses against C
(so) B looses against A, but wins against C
C wins against A, but from the second line, looses against B
So each of them has one win point and one loose point. How will you decide the winner then ?

I leave it here for you.

--------------------------

Now, into the case. Please refer to my attached file. The sheet we are referring is "Template" sheet.

I set the formula at column AL (AL6) :

Please Login or Register  to view this content.

By using this formula, we join column AC with AA, but with AC is more dominant.
For example AC6 (=16) vs AC7 (=5), if we multiply by 1000, the AC6 (=16.000) is still dominant over AC7(= 5.000).
But let say both of them have value 16 (AC6 = AC7 = 16.000), while AA6=34 and AA7=26, so the result of formula is 16,034 and 16,026. Now we can determine the winner by calculating who has the larger value (16,034 in this case).

And now for players who have same points, in this example AL6 (Carl Hadaway) and AL13 (Sean Thomas). I create another table which get the win point for players who has same value at column AL.

Select AN6:AW6 and enter this array formula (ended by pressing Ctrl Shift Enter keys) :

Please Login or Register  to view this content.

copy down until AN15:AW15

As you see, for example for Carl Hadaway (at row 6), has comparing values at AN6 (with himself) and at AU6 (with Sean Thomas), because they all have the same value at column AL (in this case 16,034).

The same thing for Sean Thomas (at row 13), has comparing values at AN13 (with Carl Hadaway) and at AU13 (with himself).

You can see "diagonal line" in this table, because for each player, at least they comparing with themselves (with value = 0, so can be neglectable).

So now just do sum for each row in this table, and the sum values is joined with the values of column AL. The same principle is the same as the previous process, column AL is multiply by 1000 (so this column is dominant) and add with the sum values.

The formula at column AY (cell AY6) :

Please Login or Register  to view this content.

This (column AY) is the final values you can use to determine the winner (there is no duplicates except for blank lines (which mean no player yet)). In this example I use rank() function at column AZ to show the rank of each players. From now you must be can continue by yourself.

Regards

Thank you so much.
I never thought of that method and probably never would! I have tried it quickly and yes it works. I will check it all works with all scenarios later when i have time but that is a brilliant piece of work.
As for you first question, when there is a draw between players there is only one solution. They all have to play another match to decide. We have had cases where even then a similar result was found and we ended up flipping a coin to decide who won. But that is backgammon, a great game to play with friends and there is a great deal of skill & luck involved.

thanks for the explanation of your formula and method approach. So many people on this site just give an answer but don't take the time to explain their solution. I think i understand it, and i will definitely try and use it on the other tables.

You are a star!

You are welcome and many thanks for the reps. Glad I can help.

Regards