You are here:

Excel/random golf pairings

Advertisement


Question
QUESTION: I am using excell 2010,
I have a golf group of 24 players that play together once a week. I would like to make up foursomes that vary from week to week so that a player will play with three differnt players each week - until everyone has played with evry one else - then repeat the sequence.
Occasionly some player will be absent - never more than 4.

Is there someway to do this with random numbers in excel. I know nothing about the use of random numbers in excel.

Thanks!

Ben

ANSWER: Ben,

This is a very complex problem apparently. It is called the Social Golfer Problem (there are related problems such as table seating) although your constraints are even more restrictive.  A bit of research says it is unsolvable meaning there is no simple mathematical solution.  I did find a breakout listed as the "best solution" by the author.  This violates your constraint of having no two people in the same grouping more than once and it does miss one pairing.  You could of course redefine who is labeled as a specific number in the groupings.
If you increased the number of weeks, to correct the missed pairing, you would get many more duplicates and if you decreased the number of weeks to reduce the duplicates, you would get more missed pairings.

You can go to this site and this listing (it also has an evaluation matrix to show you the duplicates and the missed pairing (2 and 4)

http://faculty.mercer.edu/schultz_sr/social_golfer/social_golfer.html

6 groups of 4 golfers for 9 weeks:
Best Schedule
18 21 3 15
12 13 7 6
20 19 9 22
2 14 4 16
24 1 8 10
23 5 17 11

6 9 18 17
1 20 10 14
8 16 19 3
22 12 24 11
2 5 7 15
4 21 13 23

24 14 18 19
7 22 8 21
3 17 1 13
12 20 23 15
16 5 10 9
4 2 11 6

22 1 18 5
9 23 8 3
14 6 10 15
17 4 12 19
7 16 21 11
24 20 2 13

4 18 11 8
15 13 9 22
7 19 1 23
17 20 16 14
21 10 12 2
5 3 6 24

23 3 22 2
8 12 1 5
17 4 24 15
10 7 20 18
11 21 9 14
6 13 16 19

18 2 9 1
8 20 11 13
12 14 7 3
5 15 19 21
16 23 24 6
10 17 22 4

20 5 3 4
24 17 7 21
9 19 12 2
13 18 10 23
11 15 16 1
6 22 8 14

8 2 17 15
19 11 3 10
22 16 18 12
4 7 24 9
21 6 20 1
23 14 13 5


---------
as for using random numbers:
you can put the numbers (or names of players) in A1:A24.  In B1 enter =rand()
then drag fill down column B to B24.  Select A1:B24 and sort on column B.  This will shuffle your players.  Then every 4 rows is a group of players.  

you can contact me at twogilvy@msn.com

if you have questions related to my answer.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Thanks, Tom!

1.In the first solution you gave:

a. How are the pairings made? Neither your dicussion are the web page that you referred to give any explanation of how the pairings were made. Do you just have to use the ones that he presents?
b.If less than 24 golfers are participating in a week, I guess they just have to be removed from the foresomes!

2. Using the random number method, is it best to use randbetween?; 1-24? 1-1000?

I have again thoroughly searched the web and found little that explains if and/or how to accomplish what I want to do. I did find one program for sale that does not explain what it does exactly, does not tell exactly how good the pairings are, are give a trial of the program. It cost $35
and can be found at http://www.probablegolfinstruction.com/golf-science-social-golfer-pairings-solut

If you have any other suggestions please let me know.

Thanks!

Ben

Answer
Ben,
You asked for a pattern that you could use and repeat.  That is what I gave you - a pattern.  There is no pattern that does exactly what you describe - your parameters don't fit into what you described as the desired output.   So what I gave you was advertised as an approximately best solution.

>I have again thoroughly searched the web and found little that explains if and/or how to accomplish what I want to do.

As I previously stated, my understanding is there is no simple solution so there probably won't be a simple solution shown on any web page.  If you just want random pairings and relas all you constraints, then you could do that with random numbers as previously described.    

If you want to explore algorithms then you can put this in your browser:

https://www.google.com/#q=social+golfer+problem+algorithm

But you will run into terms like combinatoric optimization and orthogonal latin squares and such.   


Using a random number generator will meet none of your constraints.  It will just shuffle the players randomly.  You can use whatever randbetween approach you want.   I suggested a way to shuffle players in my original answer using rand().  I don't know how you intend to use randbetween so I can't really say what would be best.  Say randbetween(1,24) filled into A1:A24.  You could get many duplicate entries and an equal number of missing numbers.   The rand function does not return duplicate numbers for lists even up to a million and beyond although eventually it will but not really a concern here.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.