Finding if a Cell has the Max Value after Tiebreakers

kq76

New Member
Joined
Feb 7, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I figured it out!

In case anyone searches for this in the future, this worked:

=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(ArrayFormula(($B11:$D11)*($B12:$D12)),B11*B12)=1,"Winner!",
if(B13=max($B13:$D13),if(countif(ArrayFormula(($B11:$D11)*($B12:$D12)*($B13:$D13)),B11*B12*B13)=1,"Winner!",
"Tied"),"")),"")),"")),"")

I'm sure it could be done better/simpler, but it worked for everyone tiebreaking scenario I could come up with.

The key was to realize that the only way someone could win and not tie was if their tiebreaker was not only the max, but the only of that tiebreaker combination. And the way to do that was to count if the arrayformula result was unique.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top