Hi,
I have a sheet where I'm trying to find out who (of 3 or more people) won a game. It's easy until it gets to the tiebreakers. Here's the formula in B14:
=if(B9=max($B9:$D9),if(countif($B9:$D9,B9)=1,"Winner!",
if(B11=max($B11:$D11),if(countif($B11:$D11,B11)=1,"Winner!",
if(B12=max($B12:$D12),if(countif($B12:$D12,B12)=1,"Winner!",
if(B13=max($B13:$D13),if(countif($B13:$D13,B13)=1,"Winner!","Tied"),"")),"")),"")),"")
Now the final score is in row 9. That works fine if only 1 person has the best final score.
The first tiebreaker is in row 11. That works fine if only 1 person has the max after the first tiebreaker.
The second tiebreaker in row 12 is where the problem starts (and I imagine the same could happen with the third in row 13). So let's say all 3 scores are tied in row 9. And then let's say only B11 and C11 are tied for the max first tiebreaker score (D11 is blank so they're then out of contention). But the problem occurs when B12 and D12 are tied while C12 is blank. B should be the winner as they also passed the first tiebreaker while D did not, but yet nothing is showing up for any of the cells in row 14. Yes, this is not likely to happen so I could just leave it as is, but I like learning solutions to problems.
I was wondering, can I solve this with either an array formula or sumproduct? I tried a few ways, but I'm not great at them.
Thank you!
I have a sheet where I'm trying to find out who (of 3 or more people) won a game. It's easy until it gets to the tiebreakers. Here's the formula in B14:
=if(B9=max($B9:$D9),if(countif($B9:$D9,B9)=1,"Winner!",
if(B11=max($B11:$D11),if(countif($B11:$D11,B11)=1,"Winner!",
if(B12=max($B12:$D12),if(countif($B12:$D12,B12)=1,"Winner!",
if(B13=max($B13:$D13),if(countif($B13:$D13,B13)=1,"Winner!","Tied"),"")),"")),"")),"")
Now the final score is in row 9. That works fine if only 1 person has the best final score.
The first tiebreaker is in row 11. That works fine if only 1 person has the max after the first tiebreaker.
The second tiebreaker in row 12 is where the problem starts (and I imagine the same could happen with the third in row 13). So let's say all 3 scores are tied in row 9. And then let's say only B11 and C11 are tied for the max first tiebreaker score (D11 is blank so they're then out of contention). But the problem occurs when B12 and D12 are tied while C12 is blank. B should be the winner as they also passed the first tiebreaker while D did not, but yet nothing is showing up for any of the cells in row 14. Yes, this is not likely to happen so I could just leave it as is, but I like learning solutions to problems.
I was wondering, can I solve this with either an array formula or sumproduct? I tried a few ways, but I'm not great at them.
Thank you!