formula needed to break a tie

[FreakyGirl]

hello
I need some help in figuring out a formula to break a tie between (usually) 2 players.

if there is a tie, the formula should look at the number of games played AND the total score for each player in the tie.

if the player with less games has the same or higher total than the other player they should rank higher & 1 would be returned.
if the player with less games has a lower total than the other player then should rank lower & 0 would be returned

in other words, if the number in AE is a tie, check in M then K & return a 1 or 0 in AF.

I already have a formula in AG that checks M when there's a tie & returns either K or I depending on if tied players had played the same number of games.

=IF(M13<=11,"",AE13*100+IF(AND(SUMPRODUCT(--($AE$11:$AE$99=AE13),--($M$11:$M$99=M13))=COUNTIF($AE$11:$AE$99,AE13),COUNTIF($AE$11:$AE$99,AE13)>1),K13,0))

however, i need to check BOTH M & K prior to that point in the workbook.

I have edited the formula above in different ways but I cant get it working as I need.

any help would be greatly appreciated.

please note that i just added in column AF for this formula when it was pointed out to me the ranking was wrong & the player ranked 4th should be 3rd. I will need to edit the other formulas down the line once this is working.

I also manually added in what should be returned in that column for the 2 players in a tie I am referring to.

thanks for your time,
FreakyGirl

 

[NARAYANK991]

Hi ,

I am sure someone else will help you out on your formula. I would only like to give my opinion on the way this project is being tackled.

My suggestions would be :

Start with the raw scores. Multiply them by 100 to ensure a good enough separation between scores.

Assume there will be a tie at every stage , and calculate a new score by using the first tie-breaker logic ; only ensure the addition to the score is less than say 50 , so that even by adding all the tie-breaker sums , the gap of 100 will never be crossed.

Use the second tie-breaker logic to add an amount which is less than say 5 , so that adding this amount will not change the relative scores.

Use the third tie-breaker logic to add an amount which is less than 0.5.

Keep doing this for every tie-breaker stage till you end up with a consolidated score which is the sum of all the additional tie-breaker logic amounts.

Use this final score to decide the rankings.

There is no need to use any IF statements to see whether to apply the tie-breaker or not ; just do the calculation for every player at every stage.

If the weights of the tie-breaker additional amounts are properly chosen , there should be no problem and rankings will change only where there are ties at any stage.

For this , you need to specify the complete tie-breaker logic , as many of them as are possible.

Narayan