FreakyGirl
Member
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.
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
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.
Code:
=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))
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
Attachments

145.1 KB Views: 1