Excel - non vba code - Balance and create 6 Flights based on Player Handicap

[Richard2542]

I have created the Worksheet where Players are grouped by Flight based on their Golf Handicap...heretofore the Flights have been well balanced...the exception is shown in the included screen shot. The problem appears to be created in the E Flight where I have 12 players with the exact same Hcp...any thoughts on how this could be improved. I can manually move the 12 players in question to Flight F and this would better balance the entire Roster, but I would prefer not manually override the calculations, etc. The below screen shot shows the formula I am using (column H) to determine Flights. Row 16 shows the Player distribution by Flight (the unbalanced portion is contained in both E Flight (43 Players) and F Flight (27 Players) when compared to Flight A, B, C and D.

 

[CA_Punit]

But what is Hcps

 

[Richard2542]

But what is Hcps

It is a numerical measure of the golfer's potential and is used by golfers of varying abilities to compete against each other...the better Players have lower Hcp's.
This value may change on a weekly basis and is not important in solving the above problem. The column that shows the Player Hcp is a variable. Flighting Players is based on Players Hcp's and the number of Flights that may be required for the event...the objective is to closely balance the number of Players per Flight...generally we use 5 Flights on a weekly basis, but for a Tournament we use 6 Flights...so for 200 Players that would represent approximately 33 Players per Flight.

 

[JLGWhiz]

Why not just make it a rule that when the handicaps are the same and will require a spllit of those affected golfers between flights, that it will be done based on their alphabetizede ranking. You might alternate from week to week to reverse the order.. Again, if the rules are the same for all players, the hadicap should balance the odds of turning in a decent score. I understand the email and phone call thing, but they have to learn to play by the rule and not try to intimidate the handicapper. You can stop that garbage by making a rule that if they harass the handicapper they get a full stroke deducted from their handicap. That will reduce your emails and phone calls. You will hot be able to please everybody, no matter how hard you try. I would use the most accurate formula with 2 decimal place for ranking and let the chips fall where they may.

 

[Toadstool]

Yes I devoted almost half an hour but didn't understand anything

You're probably missing the example file posted on another forum
Sample

The Handicap is ranked for the 209 players (in this example) and they are placed into one of six Flights, A to F. The wrinkle is that Richard 5422 wants to keep those with the same handicap in the same flight.

With 209 players you want about 35 (209/6) in in each Flight but because 12 players have the same Handicap of 36 it was ranking them into Flight E but then E contained 43 players and F contained 27. Richard5422 manually moved the 12 players into F resulting in E=31 players and F=39 players, so more balanced.

The question can therefore be summarised as follows. With players being assigned a Flight (A to F) based on Handicap (7.22 to 45 with duplicates) how do you assign the Flight to a player while keeping the best balance between number of players in a Flight.

 

[JLGWhiz]

disrretgard this reply

 

[Richard2542]

You're probably missing the example file posted on another forum
Sample

The Handicap is ranked for the 209 players (in this example) and they are placed into one of six Flights, A to F. The wrinkle is that Richard 5422 wants to keep those with the same handicap in the same flight.

With 209 players you want about 35 (209/6) in in each Flight but because 12 players have the same Handicap of 36 it was ranking them into Flight E but then E contained 43 players and F contained 27. Richard5422 manually moved the 12 players into F resulting in E=31 players and F=39 players, so more balanced.

The question can therefore be summarised as follows. With players being assigned a Flight (A to F) based on Handicap (7.22 to 45 with duplicates) how do you assign the Flight to a player while keeping the best balance between number of players in a Flight.

Outstanding reply...hopefully, someone has a solution - thanks again

 

[Toadstool]

Richard2542,
I'll have to give up on this. I feel there should be some kind of statistical method to better assign the Flights, but I never paid enough attention in that class.

Here's another calculation method but I believe it just moves the challenge around and doesn't resolve it. I've calculated the unique Ranks in column J, counted them in column K then assigned 1 to 6 for the A to F in column L based on reaching the average players per Flight (cell K20) for each player.
This gives Flight counts of 33, 36, 35, 35, 31, 39 instead of 35, 34, 35, 35, 43, 27 but as I said I think a different dataset would show the same kind of challenge.

I wanted to tweak by using the below/above average counts for A to F (cells M23 to E23) in the column L calculation but of course that give a circular reference.

Richard-TEST-5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
20					Flight			Flight Assignment (all Groups)		Rank Groups	34.8333		A	B	C	D	E	F
21					Play	Rpt Hcp	6 Flights	Flight Rank w/Duplicates	Toad Flight	0		1	1	2	3	4	5	6
22	AMMERMANN, NORM	GOLD	B	18.79		18.79	B	36	B	7.22	1	1	33	36	35	35	31	39
23	ANDERSON, BOB	GOLD	B	22.96		22.96	B	64	B	7.87	1	1	-2	-1	-1	0	-4	0
24	ANZIA, RONALD	BLUE	B	23.00		23	B	65	B	7.89	1	1
25	BAIL, PHIL	BLUE	F	38.19		38.19	F	191	F	9.68	2	1
26	BALOG, JOHN	GOLD	E	36.00		36	E	171	F	10.48	1	1
27	BARTHOLOMEW, BOB	BLUE	C	26.04		26.04	C	97	C	10.66	1	1
28	BAUZENBERGER, RICHARD	BLUE	B	22.15		22.15	B	59	B	11.07	1	1
29	BERGER, DAVE	GOLD	D	30.28		30.28	D	136	D	11.27	1	1
30	BEVERUNG, BUD	GOLD	E	33.32		33.32	E	160	E	12.43	1	1
31	BEWALDA, BOB	BLUE	B	21.95		21.95	B	58	B	12.66	1	1
32	BLANK, DARREL	GOLD	E	32.25		32.25	E	156	E	13.86	1	1
33	BLATNAK, STEVE	GOLD	A	14.29		14.29	A	13	A	14.29	1	1
34	BLAZICH, BOB	GOLD	C	25.90		25.9	C	94	C	14.79	1	1
35	BOESELAGER, JEROME	BLUE	A	18.39		18.39	A	34	B	14.85	1	1
Flight and Handicap

Cell Formulas
Range	Formula
K20	K20	=COUNT(GroupHcp)/6
F22:F35	F22	=IFERROR(IF(OR(OR(OR(A22="",D22=""),D22=" * "),C22="N"),"",IF(E22="X","",IF($F$16=0,ROUND(D22,0),IF($F$16=1,ROUND(D22,1),IF($F$16=2,ROUND(D22,2),""))))),"")
G22:G35	G22	=IFERROR(IF(OR($A22="",$F22=""),"",IF($H22/COUNT(GroupHcp)<=0.166,$G$15,IF($H22/COUNT(GroupHcp)<=0.3334,$H$15,IF($H22/COUNT(GroupHcp)<=0.5001,$I$15,IF($H22/COUNT(GroupHcp)<=0.6668,$J$15,IF($H22/COUNT(GroupHcp)<=0.8335,$K$15,$L$15)))))),"")
H22:H35	H22	=IFERROR(RANK(F22,GroupHcp,1),"")
I22:I35	I22	=IF(F22="","",MID("ABCDEF",INDEX($L$22:$L$250,MATCH(F22,$J$22:$J$250,0)),1))
J22:J35	J22	=AGGREGATE(15,6,GroupHcp/(GroupHcp>J21),1)
K22:K35	K22	=COUNTIF(GroupHcp,J22)
L22:L35	L22	=MIN(6,IF(SUM($K$22:$K22)>$K$20*L21,L21+1,L21))
M22:R22	M22	=SUMIFS($K$22:$K$198,$L$22:$L$198,M21)
M23:R23	M23	=SUM($M$22:M22)-(M21*$K$20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H27:H250	Cell Value	duplicates	text	NO
H22:H26	Cell Value	duplicates	text	NO
D27:D250	Cell Value	=" * "	text	NO
C27:C250	Cell Value	="N"	text	NO
A27:A250	Cell Value	duplicates	text	NO
D22:D26	Cell Value	=" * "	text	NO
C22:C26	Cell Value	="N"	text	NO
A22:A26	Cell Value	duplicates	text	NO

 

[Richard2542]

Toadstool...Thank you sooooooo much. I'm going to take a close look at this - you may have a workable solution, however, I have Round 2 of the Tournament tomorrow and Wednesday, so it will be a few days before I can begin. You have been a great ally in this undertaking - I'll let you know and thanks again.