Find teams using a found average

[Gulfcourse]

I have a small golf league that will be having a scramble outing and would like to know if its possible to auto calculate the fairest 4 person teams using each golfers handicap?
My list consists of 27 rows players with Column A as the Last Name, B is first name, C is # of rounds they have played this year, D is their handicap, and E is either 1(YES) or 0(NO) to tell me if they should be inclued.
I'm limiting participation to 20 of the 27, hence column E.
Based on column D, handicap, I'd like to put together FIVE 4 person teams by getting the total of 4 handicaps added together, and divided by four, as close as possible.

 

[Burrgogi]

I'm limiting participation to 20 of the 27, hence column E.

27 does not make any sense. If you want 4-person teams, you should pick a number that is in multiples of 4. e.g. 20, 24, 28.

Nevermind. I re-read your posting. Sounds like you want to cut the field to 20 players total. Just eliminate the players with the highest handicap and go from there. By the way, I see that both George & Jim on your list have a ZERO handicap (which in itself is very rare for recreational players) and yet they haven't played a single round this year. I find that very hard to believe.

 

[Gulfcourse]

27 does not make any sense. If you want 4-person teams, you should pick a number that is in multiples of 4. e.g. 20, 24, 28.

Nevermind. I re-read your posting. Sounds like you want to cut the field to 20 players total. Just eliminate the players with the highest handicap and go from there. By the way, I see that both George & Jim on your list have a ZERO handicap (which in itself is very rare for recreational players) and yet they haven't played a single round this year. I find that very hard to believe.

They don't have enough scores to calculate a handicap and probably will not play in the scramble.
Thats the reason for column E, if marked with a 1, they are the ones playing so I cannot just drop random handicaps.

 

[Burrgogi]

What rule(s) are you using to determine whether someone should be included or not? For example, I see that both John & Earl have the same handicap scores (15.2) but you are excluding Earl from golf outing. Not all of us are golf experts. Maybe you should provide some details on your methodology or reasoning whether a player is included or excluded.

 

[Gulfcourse]

My determination and reasoning are not the issue here, if there is the #1 in column E, they are playing.
My problem is to create 5 teams, that are near as equal as possible using handicaps, from the golfers with 1 in column E.
If I do it manually, I come up with 5 teams, each of them with handicaps that total between 54.24 and 54.72.
As the season goes on, the handicaps change and if possible, I would like a formula to do it for me.
I hope that explains it.
Thank you

 

[Burrgogi]

OK I see, you don't need any help from us on whether a certain player is included or excluded. You just want help on how to form 4-man teams based on their handicap scores and nothing else? I don't know of any way to help you using just a formula solution. Maybe someone else will come up with some ideas. Good luck.