Sort and create golf scramble teams of 3 some's using equal handicaps

[geol]

Thanks to shg I found his formula to sort and create golf 4 some's into teams balanced into equal handicaps. Now I am trying to figure how to modify that formula (or use something different) to create teams with 3 people in each team with equal handicaps. I have attached a spreadsheet showing how the formula works with both teams of 4 and teams of 3.

[JeteMc]

This proposed solution is not as elegant as SHG's however it will work (due to the handicaps being sorted), and might be useful if the scramble that you are arranging is for today.
1) Put the number one in cell H4,
2) Select cell H5 and paste the following formula into the formula bar:

Please Login or Register  to view this content.

3) Press the Enter key then double click the fill handle for cell H5 to copy down to H12 (or wherever the actual data ends).
For the sample teams one and two should display an average handicap of 16 while team three has an average handicap of 17.
Let us know if you have any questions.

[geol]

Thanks JeteMc. That works great for the 3 some's. How should I modify it for teams of 4 like I have in my second example? I keep getting a #REF! in rows 4-12. Thanks in advance for your help.

[JeteMc]

I had assumed that you would continue to use SHG's formula for teams of 4, however to modify my solution (remember the range F20:G31 has to be sorted by handicap):
1) Put the number one in cell H20,
2) Select cell H21 and paste the following formula into the formula bar:

Please Login or Register  to view this content.

3) Press the Enter key then double click the fill handle for cell H21 to copy down to H31 (or wherever the actual data ends).
Let us know if you have any questions.

[geol]

Thanks this is great. When I apply your formula to variable numbers of players (11, 13, 16, 24, etc) it comes very close to producing equal team averages for both the 3 some's and the 4 some's. A little manual adjustment at the end balances the teams a little better. You are the expert!

[JeteMc]

Your Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[TheRed99]

I am looking for such an Excel to do schedule for my club for next Monday. We have 33 players for teams of 3. Do I just copy and paste row 12 up to 33 rows ? Thanks

[6StringJazzer]

I am looking for such an Excel to do schedule for my club for next Monday. We have 33 players for teams of 3. Do I just copy and paste row 12 up to 33 rows ? Thanks

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread