Sort winners of a golf tournament two ways

[Barney]

In a golf tournament, we have two groups of winners. One group is those
players with the lowest gross scores. The second group is those players
with the lowest net (gross minus handicap) scores. In the example, 3
players would be rewarded for their highest finish, be it gross or net.
They cannot win a prize in both categories.

Example:

Name Hdc Gross Scr Net Scr Gross Rank Net
Rank
Bob 12 87 75 6
6
Carl 6 78 72 2
4
Ed 17 86 69 5
1
Marty 15 85 70 4
2
Rob 8 79 71 3
3
Steve 10 88 78 7
7
Tom 4 77 73 1
5

The list should sort with the gross winners on top like this:

Name Hdc Gross Scr Net Scr
Tom 4 77 73 (1st gross)
Carl 6 78 72 (2nd gross)
Rob 8 79 71 (3rd gross)
Ed 17 86 69 (1st net)
Marty 15 85 70 (2nd net)
Bob 12 87 75 (3rd net)
Steve 10 88 78 (out of the
money)

The actual list of players is much longer than this example. About half the
field will finish in the money. There are tie breakers which will have to
sorted and be considered in the rankings.

Any help will be appreciated.

Barney

 

[KateB]

Barney,

I would do a ascending sort on "gross score" on all the data. Then I
would select the all the rows, except the first 3 (the ones with the
lowest gross score) & ascending sort based on the net score. This
means the required 6 players should be at the top of the list.

I'm not a golfer, so I'm not sure which prize takes precedence if you
win both (i.e. lowest gross & net score). In my example, it assumes
you win the gross score first.

Hope this helps,
Kate

 

[Barney]

Kate,

Thanks for your sorting suggestion. Your thought that gross would awarded
first is correct. The value of the prizes are the same, place for place,
i.e. 1G = 1N, 2G = 2N etc.

Unfortunately, sorting the way you suggest will not work because a player is
paid for the highest position that he earns. For instance using your
method, if a player is 3rd gross and 2nd net, he would be paid for 3rd gross
and not 2nd net.

Any more thoughts?

Thanks again,

Barney


Barney,

I would do a ascending sort on "gross score" on all the data. Then I
would select the all the rows, except the first 3 (the ones with the
lowest gross score) & ascending sort based on the net score. This
means the required 6 players should be at the top of the list.

I'm not a golfer, so I'm not sure which prize takes precedence if you
win both (i.e. lowest gross & net score). In my example, it assumes
you win the gross score first.

Hope this helps,
Kate

 

[Stewart Gordon]

Kate,

Thanks for your sorting suggestion. Your thought that gross would awarded
first is correct. The value of the prizes are the same, place for place,
i.e. 1G = 1N, 2G = 2N etc.

Unfortunately, sorting the way you suggest will not work because a player is
paid for the highest position that he earns. For instance using your
method, if a player is 3rd gross and 2nd net, he would be paid for 3rd gross
and not 2nd net.

If 1st gross and 1st net would go to the same player, does the next
player down win 1st net, or does the 1st net prize just disappear?

Any more thoughts?

<snip top of upside-down reply>

Assuming the former, here's the way I've found of doing it. Let columns
A, B, C and D be name, handicap, gross score and net score
respsectively. Then make column E be "Gross Down", defined with E2 as

=C2-MIN(C2:C$9)

and similarly down to the bottom. (The 9 is one below the bottom of the
data.) Essentially, it's the amount by which the player's score exceeds
the lowest score from this point down in the list; the aim is to sort
some of the players by gross score and thus make this always zero for
those players who will be determined to win a gross prize. Similarly,
put in F2 ("Net Down" column)

=D2-MIN(D2$9)

Now make column I alternate values of TRUE and FALSE. (Using column I
leaves a gap between the data that must be sorted and these values,
which must remain alternating, so that using the sort toolbar/ribbon
button omits this column.)

Now select the right "Down" value into column G, with

=IF(I2,E2,F2)

Then repeatedly sort on this column until all values go to zero.

You may get to a point at which it will keep flipping between two or
more orders. If this happens, shuffle (e.g. by sorting by name) the
rows from the first non-zero in column G downwards and then try again.

The end result is a table sorted by 1st gross, 1st net, 2nd gross, 2nd
net and so on.

That's before you consider ties for a place, which you haven't addressed
yet and which might make the solution more complicated....

HTH. HAND

Stewart.

 

[Dick Kusleika]

In a golf tournament, we have two groups of winners. One group is those
players with the lowest gross scores. The second group is those players
with the lowest net (gross minus handicap) scores. In the example, 3
players would be rewarded for their highest finish, be it gross or net.
They cannot win a prize in both categories.

Example:

Name Hdc Gross Scr Net Scr Gross Rank Net
Rank
Bob 12 87 75 6
6
Carl 6 78 72 2
4
Ed 17 86 69 5
1
Marty 15 85 70 4
2
Rob 8 79 71 3
3
Steve 10 88 78 7
7
Tom 4 77 73 1
5

The list should sort with the gross winners on top like this:

Name Hdc Gross Scr Net Scr
Tom 4 77 73 (1st gross)
Carl 6 78 72 (2nd gross)
Rob 8 79 71 (3rd gross)
Ed 17 86 69 (1st net)
Marty 15 85 70 (2nd net)
Bob 12 87 75 (3rd net)
Steve 10 88 78 (out of the
money)

The actual list of players is much longer than this example. About half the
field will finish in the money. There are tie breakers which will have to
sorted and be considered in the rankings.

In a new column, put

=(IF(E3>3,999,E3)*1000)+F3

Where E is Gross Rank and F is Net Rank. This assumes you will have less
than 1000 players. The sort on this new column.

See here http://www.dailydoseofexcel.com/archives/2004/12/30/football-pool/

for one technique on ties. Note the Rank1 and Rank2 columns use the above
technique (sort of). Check out the payout range all the way at the bottom
for hoiw, for instance, 2nd and 3rd split the pooled winnings.

 

[Barney]

Stewart,

A single player cannot win two prizes. If he qualifies to win two, get
get the highest one, either gross or net. Then the next player in line gets
the other prize.

Ties are broken as follows:
Gross prizes: 1st tie breaker - lowest gross last 18 holes then

2nd tie breaker - lowest gross holes 10-18
then

3rd tie breaker - lowest gross holes 13-18
then

4th tie breaker - lowest gross holes 16-18

Net prizes: 1st tie breaker - lowest net last 18 holes then

2nd tie breaker - lowest net last holes 10-18 using ½
handicap then

3rd tie breaker - lowest net last holes 13-18 using 1/3
handicap then

4th tie breaker - lowest net last holes 16-18 using 1/6
handicap

If ties are still not broken, a coin toss will be used to determine winners.
The Committee Chairman will toss the coin.

Here are two pictures of the spreadsheet we used last year showing one
flight only. One is sorted by gross scores using the gross tie-breaker
rules. The other is sorted by net scores using the net tie-breaker rules.
We printed out each sort and then worked them mannually, one against the
other to determine winners. It seems there would be an easier way.

Grand Sr 70+ Gross Sort
Day 1 Day 2 Total Rank Rank
Player Hcp Gr Net Gr Net L9G L9N L6G L6N L3G L3N Gr Net Gr Net
Yacalavitch, John 8 77 69 73 65 35 31.0 24 24.0 12 12.0 150 134 1 3
Tucker, John 10 76 66 74 64 35 30.0 23 23.0 14 14.0 150 130 1 1
Koepplin, Bill 10 75 65 79 69 37 32.0 24 24.0 13 13.0 154 134 3 3
Mizell, Wally 8 79 71 76 68 37 33.0 23 23.0 14 14.0 155 139 4 8
Anderson, Andy 12 75 63 80 68 40 34.0 26 26.0 15 15.0 155 131 4 2
Champion, Malcolm 10 83 73 76 66 39 34.0 25 25.0 14 14.0 159 139 6 8
Manuele, Mike 10 81 71 78 68 37 32.0 24 24.0 14 14.0 159 139 6 8
LaBarbara, A.J. 9 80 71 82 73 38 33.5 25 25.0 14 14.0 162 144 8 15
Andrzejewski, Dave 13 81 68 82 69 42 35.5 26 26.0 15 15.0 163 137 9 6
Commons, Mike 12 82 70 82 70 36 30.0 23 23.0 13 13.0 164 140 10 11
Healey, Warren 12 81 69 87 75 44 38.0 27 27.0 15 15.0 168 144 11 15
Garrity, Jack 11 86 75 83 72 41 35.5 28 28.0 14 14.0 169 147 12 19
Brennock, Bob 13 86 73 83 70 43 36.5 30 30.0 17 17.0 169 143 12 14
Haws , Don 15 83 68 87 72 45 37.5 31 31.0 16 16.0 170 140 14 11
Purnell, Clement 12 84 72 87 75 41 35.0 27 27.0 15 15.0 171 147 15 19
Hickman, Tom 15 88 73 88 73 44 36.5 30 30.0 17 17.0 176 146 16 18
Barnes, Emory 12 85 73 91 79 45 39.0 29 29.0 18 18.0 176 152 16 23
Marks, Rae 20 87 67 90 70 47 37.0 31 31.0 19 19.0 177 137 18 6
Moody, Ed 19 88 69 94 75 48 38.5 32 32.0 20 20.0 182 144 19 15
King , Miles 16 86 70 96 80 48 40.0 28 28.0 15 15.0 182 150 19 21
Glover, Bob 18 93 75 94 76 46 37.0 30 30.0 19 19.0 187 151 21 22
Strickland, Earl 27 95 68 93 66 47 33.5 30 30.0 18 18.0 188 134 22 3
Zorabedian, John 25 97 72 93 68 45 32.5 30 30.0 17 17.0 190 140 23 11
Wells, Buford 20 100 80 92 72 49 39.0 34 34.0 20 20.0 192 152 24 23


Grand Sr 70+ Net Sort
Day 1 Day 2 Total Rank Rank
Player Hcp Gr Net Gr Net L9G L9N L6G L6N L3G L3N Gr Net Gr Net
Tucker, John 10 76 66 74 64 35 30.0 23 23.0 14 14.0 150 130 1 1
Anderson, Andy 12 75 63 80 68 40 34.0 26 26.0 15 15.0 155 131 4 2
Yacalavitch, John 8 77 69 73 65 35 31.0 24 24.0 12 12.0 150 134 1 3
Strickland, Earl 27 95 68 93 66 47 33.5 30 30.0 18 18.0 188 134 22 3
Koepplin, Bill 10 75 65 79 69 37 32.0 24 24.0 13 13.0 154 134 3 3
Andrzejewski, Dave 13 81 68 82 69 42 35.5 26 26.0 15 15.0 163 137 9 6
Marks, Rae 20 87 67 90 70 47 37.0 31 31.0 19 19.0 177 137 18 6
Champion, Malcolm 10 83 73 76 66 39 34.0 25 25.0 14 14.0 159 139 6 8
Manuele, Mike 10 81 71 78 68 37 32.0 24 24.0 14 14.0 159 139 6 8
Mizell, Wally 8 79 71 76 68 37 33.0 23 23.0 14 14.0 155 139 4 8
Zorabedian, John 25 97 72 93 68 45 32.5 30 30.0 17 17.0 190 140 23 11
Commons, Mike 12 82 70 82 70 36 30.0 23 23.0 13 13.0 164 140 10 11
Haws , Don 15 83 68 87 72 45 37.5 31 31.0 16 16.0 170 140 14 11
Brennock, Bob 13 86 73 83 70 43 36.5 30 30.0 17 17.0 169 143 12 14
LaBarbara, A.J. 9 80 71 82 73 38 33.5 25 25.0 14 14.0 162 144 8 15
Healey, Warren 12 81 69 87 75 44 38.0 27 27.0 15 15.0 168 144 11 15
Moody, Ed 19 88 69 94 75 48 38.5 32 32.0 20 20.0 182 144 19 15
Hickman, Tom 15 88 73 88 73 44 36.5 30 30.0 17 17.0 176 146 16 18
Garrity, Jack 11 86 75 83 72 41 35.5 28 28.0 14 14.0 169 147 12 19
Purnell, Clement 12 84 72 87 75 41 35.0 27 27.0 15 15.0 171 147 15 19
King , Miles 16 86 70 96 80 48 40.0 28 28.0 15 15.0 182 150 19 21
Glover, Bob 18 93 75 94 76 46 37.0 30 30.0 19 19.0 187 151 21 22
Wells, Buford 20 100 80 92 72 49 39.0 34 34.0 20 20.0 192 152 24 23
Barnes, Emory 12 85 73 91 79 45 39.0 29 29.0 18 18.0 176 152 16 23


Thanks,

Barney

 

[Barney]

What is the best way to post a sample of my 2008 spreadsheet for this
tournament?

Barney

 

[Stewart Gordon]

What is the best way to post a sample of my 2008 spreadsheet for this
tournament?

<snip top of upside-down reply>

I'm not sure what the policy of these 'groups on attachments is. Maybe
someone else can answer this. Meanwhile, do you have a website? If so,
you could upload it there and post the URL here. If you don't, maybe
this'll help you:
http://www.freewebspace.net/guide/diskstorage.shtml

Stewart.

 

[Barney]

My spreadsheet for this tournament in 2008 is here:

www.jaxareagolfassn.com/2008SeniorScoringExpanded.xls

The spreadsheet has macros so you may want to check it before opening.

Each flight has its own buttons at the bottom of the flight for sorting.

I sort each flight by 'gross' and 'net' rank. Printing out each sort on
paper. The sorts do all of the tie breakers except the coin toss as a last
resort. I then manually work one list agains the other to get the finishing
positions for all the winner. A very laborious and error prone process.

Ties are broken as follows:
GROSS:
1st tie breaker - lowest gross last 18 holes then
2nd tie breaker - lowest gross holes 10-18 then
3rd tie breaker - lowest gross holes 13-18 then
4th tie breaker - lowest gross holes 16-18
NET:
1st tie breaker - lowest net last 18 holes then
2nd tie breaker - lowest net last 9 holes using ½ handicap then
3rd tie breaker - lowest net last 6 holes using 1/3 handicap then
4th tie breaker - lowest net last 3 holes using 1/6 handicap

If ties are still not broken, a coin toss will be used to determine winners.

Keep in mind; A single player cannot win two prizes. If he qualifies to win
two, he gets
highest ranking one, either gross or net.

I hope this clears up everyone's questions and thanks for all the help.

Barney

 

[Dick Kusleika]

Ties are broken as follows:
GROSS:
1st tie breaker - lowest gross last 18 holes then
2nd tie breaker - lowest gross holes 10-18 then
3rd tie breaker - lowest gross holes 13-18 then
4th tie breaker - lowest gross holes 16-18
NET:
1st tie breaker - lowest net last 18 holes then
2nd tie breaker - lowest net last 9 holes using ½ handicap then
3rd tie breaker - lowest net last 6 holes using 1/3 handicap then
4th tie breaker - lowest net last 3 holes using 1/6 handicap

Barney: Insert the following formulas in Row 4 and fill down to row 43.

BA4:
=IF(ISERROR(AY4),"",TEXT(AY4,"000")&TEXT(AO4,"000")&TEXT(AQ4,"00")&TEXT(AS4,"00")&TEXT(AU4,"00"))

BB4:=IF(ISERROR(AY4),"",VALUE(BA4))

BC4: =IF(ISERROR(AY4),"",RANK(BB4,$BB$4:$BB$43,1))

BD4:
=IF(ISERROR(AY4),"Z",IF(BC4<=3,"G"&BA4,"N"&TEXT(AZ4,"000")&TEXT(AP4,"000")&TEXT(AR4,"00")&TEXT(AT4,"00")&TEXT(AV4,"00")))

Sort on column BD.

 

[Barney]

Barney: Insert the following formulas in Row 4 and fill down to row 43.

BA4:
=IF(ISERROR(AY4),"",TEXT(AY4,"000")&TEXT(AO4,"000")&TEXT(AQ4,"00")&TEXT(AS4,"00")&TEXT(AU4,"00"))

BB4:=IF(ISERROR(AY4),"",VALUE(BA4))

BC4: =IF(ISERROR(AY4),"",RANK(BB4,$BB$4:$BB$43,1))

BD4:
=IF(ISERROR(AY4),"Z",IF(BC4<=3,"G"&BA4,"N"&TEXT(AZ4,"000")&TEXT(AP4,"000")&TEXT(AR4,"00")&TEXT(AT4,"00")&TEXT(AV4,"00")))

Sort on column BD.

Dick,

Thanks for your help.

My example showed only three gross winners, but we usually pay at least
6 or more gross and net in each flight. With that in mind, I revised the
4th formula by changing BC4<=3 to BC4<=6.

Then when I resorted I noticed that Andy Anderson who finished 4th gross
and 2nd net was being paid the 4th gross award instead of the 2nd net award
which is higher.

Keep in mind that players are paid for their highest finish. Can this
problem be fixed?

Barney

 

[Dick Kusleika]

Then when I resorted I noticed that Andy Anderson who finished 4th gross
and 2nd net was being paid the 4th gross award instead of the 2nd net award
which is higher.

Keep in mind that players are paid for their highest finish. Can this
problem be fixed?

Yep. Try this:

BA4
=IF(ISERROR(AY4),"",TEXT(AY4,"000")&TEXT(AO4,"000")&TEXT(AQ4,"00")&TEXT(AS4,"00")&TEXT(AU4,"00"))
BB4 =IF(ISERROR(AY4),"",VALUE(BA4))
BC4 =IF(ISERROR(AY4),"",RANK(BB4,$BB$4:$BB$43,1))
BD4
=IF(ISERROR(AY4),"",TEXT(AZ4,"000")&TEXT(AP4,"000")&TEXT(AR4,"00")&TEXT(AT4,"00")&TEXT(AV4,"00"))
BE4 =IF(ISERROR(AY4),"",VALUE(BD4))
BF4 =IF(ISERROR(AY4),"",RANK(BE4,$BE$4:$BE$43,1))
BG4 =IF(BC4<=BF4,BC4,99000+BF4)
BH4
=IF(ISERROR(AY4),"Z",IF(RANK(BG4,$BG$4:$BG$43,1)<=6,"G"&RANK(BG4,$BG$4:$BG$43,1),IF(BG4>99000,"N"&RANK(BG4,$BG$4:$BG$43,1),"Z")))

Then sort on BH. Positions 1-6 will be the Gross winners, positions 7-12
will be the Net winners.

 

[Barney]

Dick,

It is working perfectly. I have been able to change the number of gross
winners to anything I want, but the net winners seems to be limited to 9.
What do I do to increase or decrease that number?

Thanks for all your help on this. I can tell that you put a lot of
effort into this great improvement.

Barney

 

[Dick Kusleika]

Dick,

It is working perfectly. I have been able to change the number of gross
winners to anything I want, but the net winners seems to be limited to 9.
What do I do to increase or decrease that number?

Yeah, that's going to be a potential problem. The 9 is the number of
golfers whose net rank is less than their gross rank. It works in this
example if you pay nine or less places, but it may not work in another
example regardless of the number of places.

I didn't see a quick fix. I think we need to go the VBA route.

 

[Barney]

Dick,

Don't do any more work until I find out if they ever pay more than 9
places. You have done a lot and I really appreciate your efforts.

Barney

 

[Barney]

Dick,
In the example shown at http://jaxareagolfassn.com/2009SeniorScoring.xls
it looks like John Milton on row 20 should have been paid for 6th gross
instead of 7th net. The two players on rows 18 & 19 were paid in the net
division where they were fifth. That means that Milton would get paid for
6th gross which pays more than 7th net.

Any suggestions?

Thanks, Barney