Excel Formula to determine a winner team

G

gary2014

Board Regular
Joined
Mar 31, 2012
Messages
67
Hi Team,

Please help with a command to achieve the following:
1) I want the "
Winning Team" to populate in that column depending on scores of Team A & Team B.​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Match No[/TD]
[TD="align: center"]Team A[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Team B[/TD]
[TD="align: center"]Score Team A[/TD]
[TD="align: center"]Score Team B[/TD]
[TD="align: center"]Winning Team[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Kings College[/TD]
[TD="align: center"]Vs[/TD]
[TD="align: center"]Royal College[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]Plz help me with the code[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]AV College[/TD]
[TD="align: center"]VS[/TD]
[TD="align: center"]Royal College[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]Plz help me with the code[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Kings College[/TD]
[TD="align: center"]VS[/TD]
[TD="align: center"]AV College[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]Plz help me with the code[/TD]
[/TR]
</tbody>[/TABLE]

2) I want the "
Winner and Runner" to populate in two different columns depending on cumulative scores.​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]Kings College[/TD]
[TD="align: center"]Royal College[/TD]
[TD="align: center"]AV College[/TD]
[TD="align: center"]Cumulative Score[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Winner Team[/TD]
[TD="align: center"]Runner Up Team
[/TD]
[/TR]
[TR]
[TD="align: center"]Kings College[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]80[/TD]
[TD="align: center"][/TD]
[TD="align: center"]--??--[/TD]
[TD="align: center"]--??--[/TD]
[/TR]
[TR]
[TD="align: center"]Royal College[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxxxx[/TD]
[TD="align: center"]xxxxxxxx[/TD]
[/TR]
[TR]
[TD="align: center"]AV College[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxxxx[/TD]
[TD="align: center"]xxxxxxxx[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
For 1
Assuming your table is in a1:g4
In G2, =if(e2>f2,b2,d2)

you don't say what you would like in the event of a draw!!!
 
Upvote 0
Hi Steve,
Thanks for the reply.
Every match with end with a result, if the scores are equal at the end of playing time, the teams will play for extra time.

For 2.

Please help me with my question number 2.

I would like to determine only "Winner" & and "Runner Up" from a points table.

I have attached file for your reference.

http://www18.zippyshare.com/v/55691077/file.html

Please click on the above link to locate the file (Zippyshare).
 
Upvote 0
In your example, would kings college & royal college be winner & runner-up?
Again, what would happen in the even of a tie??????????
 
Upvote 0
I have change the file exactly fulfill my requirement.

I have attached a file for your reference.

Zippyshare.com - Winner & Runner.xlsx
Please click on the above link to extract the file.


In this type of tournament, every match with end with a result as winner or loser, if the scores are equal at the end of playing time, the teams will play for extra time of 10 minutes.

Thank you in advance.
 
Upvote 0
You will have to post samples of your data on here.

Due to the risk of virus etc, am unable to download files from external sources

The IT police would go balistic
 
Upvote 0
:stickouttounge:

Sure!

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Game 1[/TD]
[TD]Game 2[/TD]
[TD]Game 3[/TD]
[TD]Game 4[/TD]
[TD]Game 5[/TD]
[TD]Points[/TD]
[TD]Winner Team with highest points[/TD]
[TD]Runner up team with second highest points[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Harvard College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="align: center"]
30
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="align: center"]56[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="align: center"]45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="align: center"]37[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="align: center"]41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="align: center"]209[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]I should get the winner team here[/TD]
[TD="align: center"]and Runner up team here[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Yale College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]227[/TD]
[TD]xxxxxxxx[/TD]
[TD]xxxxxxxx[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Mellon College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]199[/TD]
[TD="align: center"]xxxxxxxx[/TD]
[TD]xxxxxxxx[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]McGill College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]248[/TD]
[TD]xxxxxxxx[/TD]
[TD]xxxxxxxx[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD]Purdue College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]198[/TD]
[TD]xxxxxxxx[/TD]
[TD]xxxxxxxx[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sir, I have presented the date above in my previous thread, please let me know if you require more information.
 
Upvote 0
ok, assuming your table is in a1:i6.
in j2 and fill down =RANK(G2,$G$2:$G$6) - where col G holds the cumulative total
in k2 and fill down =+A2 - to refer to your team names
in h2 =VLOOKUP(1,J:K,2,FALSE) - should return the name of the team with the highest ranked score
in h3 =VLOOKUP(2,J:K,2,FALSE) - should return the name of the team with the 2nd highest ranked score
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Extract Team Pairs from Table
Replies
2
Views
240
Steve 1962
S
F
  • Question Question
Scoresheet creation for a trivia game
Replies
1
Views
372
Rijnsent
Rijnsent
J
  • Solved
IFS formula with TIME
2
Replies
13
Views
1K
adunn_23
A
VinceF
  • Question Question
Formula To Show Result
Replies
1
Views
610
VinceF
VinceF
Z
  • Solved
populate data based on multiple criteria
Replies
7
Views
336
Cubist
Cubist

Forum statistics

Threads
1,225,768
Messages
6,186,924
Members
453,387
Latest member
uzairkhan

Share this page

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