Formula to find 3 winners using 3 columns of data

[kdlenort]

I am trying to create a formula to find the winners of a tournament based on how many wins (column C), the winners score (column D) and the opponents score (column E).

The team numbers are listed in column A.
The players are listed in column B.

1 Larry/Tom 4 59 63
2 Terry/Dolan 3 66 59
3 Nick/Travis 5 69 52
4 James/Brent 5 64 61
5 Jay/George 2 50 66
6 Chris/Jordan 3 61 58
7 Del/Shawn 2 52 68
8 Kyle/Matt 4 61 55

Winners should be:
1. Team 3 with 5 wins and 69 points
2. Team 4 with 5 wins and 64 points
3. Team 8 with 4 wins and 61 points

I'm thinking possibly indexing but not sure how.

Any help is appreciated!


Thanks!!

 

[inactiveuserps07]

@KDLenort, I'm a little confused.

Why are some of the scores in D (winner's score) lower than the scores in E (opponent's score)?

Using your entry for Team 1 (first entry), are Larry and Tom playing together as one team? Or are they playing against one another?

 

[DRSteele]

I assume what you're really after is a ranking of each team. The way to rank teams when tie-breakers are required is to add the various statistics together, all-the-while ensuring that each new tiebreaker is an appropriate order of magnitude smaller than the previous. In your case, I assume we will first rank by Wins, and if there are ties (like Teams 3 and 4) then we will rank by Winner's Score [and Winner's has an apostrophe], and if there are still ties then by Opponent's Score [and, of course, Opponent's has an apostrophe]. I guessed that the Winner's Score will never go over 100, and the same for the Opponent's Score, but those can be altered. The next step is to make a new statistic, which I called Grade (a column that can be hidden, if you like). Finally, all that needs doing is to Rank them by that statistic. Something like this:

	A	B	C	D	E	F	G
Larry/Tom
Terry/Dolan
Nick/Travis
James/Brent
Jay/George
Chris/Jordan
Del/Shawn
Kyle/Matt

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Max Winner's Score[/TD]
[TD="bgcolor: #FCE4D6"]Max Oppenent's Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F8CBAD, align: right"]100[/TD]
[TD="bgcolor: #F8CBAD, align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC"]Team[/TD]
[TD="bgcolor: #FFF2CC"]Names[/TD]
[TD="bgcolor: #FFF2CC"]Wins[/TD]
[TD="bgcolor: #FFF2CC"]Winner's Score[/TD]
[TD="bgcolor: #FFF2CC"]Oppenent's Score[/TD]
[TD="bgcolor: #FFE699"]Grade[/TD]
[TD="bgcolor: #FFE699"]Rank[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]63[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4.5963[/TD]
[TD="bgcolor: #A9D08E, align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]59[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3.6659[/TD]
[TD="bgcolor: #A9D08E, align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]52[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5.6952[/TD]
[TD="bgcolor: #A9D08E, align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]61[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5.6461[/TD]
[TD="bgcolor: #A9D08E, align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]66[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2.5066[/TD]
[TD="bgcolor: #A9D08E, align: right"]8[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]58[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3.6158[/TD]
[TD="bgcolor: #A9D08E, align: right"]6[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]68[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2.5268[/TD]
[TD="bgcolor: #A9D08E, align: right"]7[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]55[/TD]
[TD="bgcolor: #E2EFDA, align: right"]4.6155[/TD]
[TD="bgcolor: #A9D08E, align: right"]3[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]=C5+D5/$D$2+E5/($D$2*$E$2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=RANK.EQ(F5,$F$5:$F$12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

And if you don't know what the maximums will be in the various columns, you can use these formulas to calculate them.

	D	E

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Max Winner's Score[/TD]
[TD="bgcolor: #FCE4D6"]Max Oppenent's Score[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #A9D08E, align: right"]100[/TD]
[TD="bgcolor: #A9D08E, align: right"]100[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=10^MAX(LEN(D5:D12))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=10^MAX(LEN(E5:E12))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[kdlenort]

ErikTyler
The scores are in pickleball games and involve 8 teams (Team 1 is Larry and Tom) and at least 7 rounds of games (if not more).

 

[kdlenort]

DRSteele Thanks.
I am not after the ranking of each team at this time. Thanks tho this might come in handy at a later time. all I need is a formula for 3 winners based on the 1st column then the second column and finally the 3rd column (if it comes into play).

 

[kdlenort]

Hi
I tried out your ranking idea and I am getting #NAME? for the answers in column G.
Everything else is now you have your sheet set up.
Any idea why?

Update
I took out the .EQ and am coming up the same answers you did.

Thanks

 

[kdlenort]

Thanks so much your ranking formulas did work to find 1st, 2nd and 3rd places.

Now I need a way to search the array to find rank 1 and bring the name of the team to cell B20, and rank 2 team name to cell B21 and rank 3 team name to cell B22.

I tried vlookup formula but it's throwing out a #NA error.

 

[Oeldere]

please show us the data.

I gues it can be done with Vlookup or (index/match)

 

[kdlenort]

I was able to work it out with (index/match)

INDEX($H$5:$H$12,MATCH(1,$G$5:$G$12,0))
I changed the number in the MATCH to 2 for 2nd place and 3 for 3rd place. All working now.

Thanks so much everyone!!