Formula to populate cell with winning team name

[Oldgold]

Hello

I hope someone could help with this problem I am having. I am fairly new to the world of excel functions so if there is a most basic way of resolving my problem I would be most grateful.

I have a worksheet that calculates performance of three teams, and scores points for each team based on the performance compared to targets. Everything works fine. I have each team adjacent, so for example Team 1 in column A, Team 2 in column B and Team 3 in column C.
At the bottom of each column is the teams total points score.

What I now want to do is automatically display in a another cell, the winning team name based on the scores (say team a wins, their name is in A1, so I want the contents of A1 to appear in this new cell. However, if more than one team wins, so say team a and team b finish with 10 points and team c with 5, I would like the new cell to show either "draw" or, preferably (if its possible and not too complicated) to show both the winning team names.

I've messed about with nested IF commands and the rank function, but cannot get it to work properly and am beginning to get more and more confused.

Any help would be appreciated.

Thanks
Kevin

[NBVC]

If your teamnames are in A1:A3 and your total scores are in A10:C10..

First extract the scores from largest to smallest with a formula like

=LARGE($A$10:$C$10,1) copied down

Then adjacent cell use formula

=INDEX($A$1:$C$1,SMALL(IF($A$10:$C$10=F2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),COUNTIF($F$2:F2,F2)))

which assumes you put the Large() formula in F2 and copied down to F4....

This last formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down.


See attached...

[Oldgold]

Thanks for that. That's very useful and actually helps on another part of the workbook.

Is it possible to add another column to this, such as a secondary scoring column, to order the teams if the points were the same. For example, if all three teams had 6 points, but a secondary scoring measure had team1 with 5, team2 with 4 and team3 with 3, they would be displayed in the order team1, team2, team3. Like a football table, teams are ordered based on points, but if they are level, the goal difference kicks in and the one with the better goal difference will then be the higher placed team.

Please ignore the attachment, this was for a different post, but want to go with this way of working described above.

Many thanks.

[NBVC]

Thanks for that. That's very useful and actually helps on another part of the workbook.
...

Please ignore the attachment, this was for a different post, but want to go with this way of working described above.

Many thanks.

Please, then add an attachment showing an example of what you mean.

[Oldgold]

Sorry about that.

Have now added an example of what I am trying to do. I have added the formulae previously provided so that the weekly table displays the team order based on points using the right hand chart totals (allocation of points table).

I now want to add a further formula so that the 'targets hit' column in the weekly table will sort the teams into order if the points are the same. So in the example attached, Team2 should be top because although they have scored 6 points each, Team2 has hit more targets (5 compared to Team1's 4). I want it to work like the sort option, where i would manually sort by Points then by Targets Hit.

Hope this makes sense and thank you for your assistance.

[NBVC]

How do you come up with the Targets Hit column in the Weekly Table?

[Oldgold]

In the example, I have just typed them in to show a possible outcome. I have now amended the example to show how I had planned to update the targets. There is a table below the weekly table which calculates each teams success/failure against each target. Then in the weekly table, i have used the sum function in the targets hit column to display the totals of the targets hit.
Hope this helps.

Thanks

[NBVC]

Ok... I think we're finally there...

See attached.

I added formula in G24:

Please Login or Register  to view this content.

confirmed with CSE keys and copied down.

I revised formula in E24 to:

Please Login or Register  to view this content.

copied down

Formula in F24:G24 remains untouched.

[Oldgold]

That seems to be working exactly how I wanted it. Thank you so much, I really appreciate your help.
I can go back to getting some sleep of a night now! Thanks again.