Tournament Scoring Problem

B

blackp

New Member
Joined
Sep 21, 2013
Messages
10
Hi, I'm a newbie here and have read tons of useful information in this forum. Apologies if this question is answered elsewhere, but I haven't found an answer which seems applicable to my lack of Excel skill.

I have been doing the scoring for a bowls tournament and the rules are that the winner is the team winning most rounds. If there are equal rounds then the total points is the deciding factor.

I have counted the number of wins and have that in a column. I can then use the RANK function to determine the order based on number of wins. All OK so far.

My problem is that RANK might return 3 ones then 2 fours followed by a 6. This is correct and understandable. If 3 teams win all 4 rounds, 2 teams win 3 rounds and one team wins 2 rounds that is the answer I would expect.

What I cannot figure out is how to take the output of RANK to the next stage.

In the example above the 3 teams with 4 wins (RANK output =1) would be 1st 2nd and 3rd based on their total points. The team with the rank of 4 with the total points would be 4th overall.

If no team had 4 wins, a team with 3 wins with the highest total points would be the overall winner, and so on.

This is probably a very dumb question, but I've been wracking my brains to come up with an answer to no avail.

Just to add another level of complication the tournament rules state that if a team has an equal number of wins and an equal number of points then the team with the highest total margin is the winner.

Here is a concatenated picture of the worksheet. At the last tournament I calculated the final placings manually:

/Users/peterblack/Desktop/Tournament.jpg

I look forward to a response.

Thanks in anticipation.

Peter.
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi blackp

Do you have something like Excel Jeanie Html to post what your data looks like? Image posted doesn't link to anything that I can see.

InaCell
 
Upvote 0
Have a look at my tag and use the HTML Maker to paste data here.
 
Upvote 0
I have installed MrExcelHTML and am trying to use that.

So far all I get is the error: "File Not Found: user32.dll" so I'm guessing this plugin only works in Windows and is of no use to me with my mac.

Catch22 methinks.
 
Upvote 0
Umm....not sure of the HTML with Mac.....maybe not
Consider uploading to a site like DropBox or MediaFire and then posting the link back here.
 
Upvote 0
InaCell said:
How's the data look if it's a straight paste into the forum?
Click to expand...

Here's a try:

[TABLE="width: 757"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD]22 Teams[/TD]
[TD] [/TD]
[TD="colspan: 2"]Game 1[/TD]
[TD="colspan: 2"]Game 2[/TD]
[TD="colspan: 2"]Game 3[/TD]
[TD="colspan: 6"]Game 4[/TD]
[TD]Total points[/TD]
[TD]Total Margin[/TD]
[TD]Wins[/TD]
[TD]Final Placing[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Team no[/TD]
[TD]Skip[/TD]
[TD]Rink Match 1[/TD]
[TD]Ends Won[/TD]
[TD]Rink Match 2[/TD]
[TD]Ends Won[/TD]
[TD]Rink Match 3[/TD]
[TD]Ends Won[/TD]
[TD]Rink Match 4[/TD]
[TD]Ends Won[/TD]
[TD]Bonus 4 +[/TD]
[TD]Win[/TD]
[TD]Sub Total[/TD]
[TD]Margin[/TD]
[TD]Overall[/TD]
[TD]Net Margin[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]J. Owens[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD="align: right"]4[/TD]
[TD]9[/TD]
[TD="align: right"]4[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]4.0[/TD]
[TD]-6[/TD]
[TD]23.0[/TD]
[TD]-18[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D. Amos[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD="align: right"]8[/TD]
[TD]14[/TD]
[TD="align: right"]6[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]9.0[/TD]
[TD]3[/TD]
[TD]42.5[/TD]
[TD]31[/TD]
[TD]3[/TD]
[TD]2nd[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]K. Brady[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD="align: right"]7[/TD]
[TD]10[/TD]
[TD="align: right"]6[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]6.0[/TD]
[TD]-2[/TD]
[TD]23.0[/TD]
[TD]-3[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]G. Gudmunson[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD="align: right"]11[/TD]
[TD]11[/TD]
[TD="align: right"]9[/TD]
[TD]3[/TD]
[TD]3.0[/TD]
[TD]15.0[/TD]
[TD]22[/TD]
[TD]39.0[/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D. Walker[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD="align: right"]9[/TD]
[TD]4[/TD]
[TD="align: right"]7[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]10.0[/TD]
[TD]3[/TD]
[TD]41.5[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]3rd[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]J. Lowe[/TD]
[TD]13[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD="align: right"]6[/TD]
[TD]3[/TD]
[TD="align: right"]8[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]11.0[/TD]
[TD]1[/TD]
[TD]36.0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]S. Smith[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD="align: right"]4[/TD]
[TD]4[/TD]
[TD="align: right"]5[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]5.0[/TD]
[TD]-3[/TD]
[TD]19.0[/TD]
[TD]-27[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]R. Gallop[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]14[/TD]
[TD="align: right"]5[/TD]
[TD]13[/TD]
[TD="align: right"]9[/TD]
[TD]1[/TD]
[TD]3.0[/TD]
[TD]13.0[/TD]
[TD]14[/TD]
[TD]36.5[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B. Prentice[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD="align: right"]7[/TD]
[TD]12[/TD]
[TD="align: right"]6[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]6.0[/TD]
[TD]-2[/TD]
[TD]40.0[/TD]
[TD]28[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B. Jaspizza[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD="align: right"]5[/TD]
[TD]9[/TD]
[TD="align: right"]8[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]11.0[/TD]
[TD]6[/TD]
[TD]29.0[/TD]
[TD]-8[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]P. Taylor[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD="align: right"]5[/TD]
[TD]11[/TD]
[TD="align: right"]3[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]3.0[/TD]
[TD]-22[/TD]
[TD]13.0[/TD]
[TD]-62[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]R. Rose[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD="align: right"]5[/TD]
[TD]12[/TD]
[TD="align: right"]6[/TD]
[TD]1[/TD]
[TD]3.0[/TD]
[TD]10.0[/TD]
[TD]2[/TD]
[TD]36.0[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]P. Ward[/TD]
[TD]11[/TD]
[TD]7[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD="align: right"]8[/TD]
[TD]6[/TD]
[TD="align: right"]9[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]12.0[/TD]
[TD]5[/TD]
[TD]47.0[/TD]
[TD]35[/TD]
[TD]4[/TD]
[TD]1st[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]J. Restaino[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD="align: right"]4[/TD]
[TD]2[/TD]
[TD="align: right"]9[/TD]
[TD]2[/TD]
[TD]3.0[/TD]
[TD]14.0[/TD]
[TD]8[/TD]
[TD]34.0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]I. Purcell[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD="align: right"]8[/TD]
[TD]5[/TD]
[TD="align: right"]5[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]5.0[/TD]
[TD]-5[/TD]
[TD]33.0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]P. Chatfield[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD="align: right"]8[/TD]
[TD]5[/TD]
[TD="align: right"]7[/TD]
[TD]1[/TD]
[TD]3.0[/TD]
[TD]11.0[/TD]
[TD]5[/TD]
[TD]30.0[/TD]
[TD]-1[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]S. Leslie[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD="align: right"]7[/TD]
[TD]14[/TD]
[TD="align: right"]6[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]6.0[/TD]
[TD]-3[/TD]
[TD]39.0[/TD]
[TD]19[/TD]
[TD]3[/TD]
[TD]4th[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]R. Hunter[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD="align: right"]7[/TD]
[TD]10[/TD]
[TD="align: right"]6[/TD]
[TD]0[/TD]
[TD]3.0[/TD]
[TD]9.0[/TD]
[TD]2[/TD]
[TD]37.0[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]B. Balmer[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD="align: right"]6[/TD]
[TD]6[/TD]
[TD="align: right"]3[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]3.0[/TD]
[TD]-5[/TD]
[TD]22.0[/TD]
[TD]-15[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]T. Thurtell[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD="align: right"]4[/TD]
[TD]3[/TD]
[TD="align: right"]4[/TD]
[TD]1[/TD]
[TD]0.0[/TD]
[TD]5.0[/TD]
[TD]-1[/TD]
[TD]28.5[/TD]
[TD]-4[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]H. Rose[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD="align: right"]1[/TD]
[TD]13[/TD]
[TD="align: right"]3[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]3.0[/TD]
[TD]-14[/TD]
[TD]16.0[/TD]
[TD]-42[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]B. Clark[/TD]
[TD]13[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD="align: right"]3[/TD]
[TD]2[/TD]
[TD="align: right"]3[/TD]
[TD]0[/TD]
[TD]0.0[/TD]
[TD]3.0[/TD]
[TD]-8[/TD]
[TD]24.0[/TD]
[TD]-10[/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi blackp

I achieved what you were after by using sort with data has headers on.

1st criteria was to sort on Wins - Largest to smallest
2nd criteria was to sort on total points - Largest to smallest
3rd criteria was to sort by margin - Largest to smallest

First is placed at the top and works it way down.

Recoded as a macro gives
Code: 
Sub Macro1()   
 Range("A3:Q24").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Q3:Q24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("O3:O24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("P3:P24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:Q24")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

I'm sure the code could be improved but it works straight from the recorder.

Probably best to just use the sort button within excel itself as the sheet can go back to normal simply by pressing undo.

Hope that helps.

InaCell
 
Upvote 0
InaCell said:
Hi blackp

I achieved what you were after by using sort with data has headers on.

1st criteria was to sort on Wins - Largest to smallest
2nd criteria was to sort on total points - Largest to smallest
3rd criteria was to sort by margin - Largest to smallest

First is placed at the top and works it way down.

Recoded as a macro gives
Code: 
Sub Macro1()   
 Range("A3:Q24").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Q3:Q24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("O3:O24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("P3:P24") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:Q24")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

I'm sure the code could be improved but it works straight from the recorder.

Probably best to just use the sort button within excel itself as the sheet can go back to normal simply by pressing undo.

Hope that helps.

InaCell
Click to expand...

Thanks very much for this. One requirement I didn't give you is that the mac is connected to a data projector and the scores and rankings are available for all to see so the displayed rows need to be in team order. That is one reason I didn't want to use sort to achieve my goals and started down the route of finding a formulaic way of getting the same result. I suppose I could mirror the salient columns in another sheet and run a macro which sorted that, then displaying the result on the main page which the projector broadcasts using VLOOKUP.

This would probably work, but I wonder still if there is an easier way to do this which I've just not discovered?

Thanks again,

Peter.
 
Upvote 0
Hi Peter

select one more col before doing the sort.

Place 1,2,3,4 in the top 4 cells.

Undo the sort.

Record it all onto a macro if required.

InaCell
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Calculating values need to beat top score
Replies
5
Views
385
Ducklings23
D
B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
2K
Peter_SSs
Peter_SSs
I
  • Question Question
Excel Spreadsheet - PGA Fedex Cup tracking
Replies
1
Views
488
Snakehips
Snakehips
S
  • Question Question
Auto Sorting League Table
Replies
2
Views
701
arun_eagertolearn
A
N
  • Question Question
Auto Sort for Leaderboard
Replies
0
Views
601
NTLKnight
N

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

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