1

Just joking, we do really.

Lets call the participants Alpha, Beta, Gamma, Delta, Epsilon, Zeta, Eta, Theta, Iota and Kappa.

How do I schedule these meetings so that all of the following are true?

  • Each participant goes to the minimum number of meeting with each other participant. That is, Alpha does not have lots of meetings with Beta and none with Gamma, but has as far as possible an equal number of meetings with everybody.

  • Each participant has one meeting a week, no more no less.

  • There are two meetings of three people, and one of four people.

  • Similar meetings are as spread out as they can be. That is, Gamma and Kappa are not in the same meeting for three weeks running.

I've smacked my head on the keyboard for a week trying to satisfy the first 3 criteria, and haven't even started on the fourth.

There is a link to the file here: https://paste.c-net.org/CreditedHopes

My approach has been: The meeting 1 field searches the list of possible 3-way meetings, for the first one which matches the three least frequent pair-ups. If N/A, it searches for two, and then for one:

(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=3,0))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=2,0)))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=1,0))))

The other two meetings formulae are similar, but include a condition not to include any of the people from the same meeting that week. The third meeting searches a seperate list of combinations of four people.

The 'least frequent pairs formula' is:

SUBSTITUTE(SUBSTITUTE(TEXTJOIN(";",TRUE,IF(COUNTIF(INDEX([Meeting 1 pair 1],1):INDEX([Meeting 3 pair 6],MATCH([@Fortnight]-1,[Fortnight],0)),Table3[All pairs])=1,Table3[All pairs])),"FALSE;",""),"FALSE","")

Where the 'meeting # pair #' cells contain each pair of meeting participants.

2
+100

Below I show that with one meeting of four people (say, i=1-4), and two of three people (say, i=5-7 and i=8-10), per week, you are already satisfying your rules 1, 2, 3. Then you rotate people to satisfy your rule 4.

I put together a simple worksheet to display the meetings as per schedule above.

enter image description here

The only considerations are:

  • The formula in E3 is =VLOOKUP(MOD(E$2+$D3-1,10),$A$3:$B$12,2), copied down and to the right.
  • You can extend the number of weeks and copy the formula as needed.
  • I used the arbitrary numbering 0-9 instead of 1-10, for convenience in formulas.
  • Numbers 0-9 in E3-N3 do not correspond with A3-A12. They are simply a way to compute the participants. If that leads to confusion for you, you could hide them, or use letters A-J instead, and CODE(q10)-CODE("A") to convert a letter in cell q10 to a number, starting with A->0.

tl;dr - "Optimal" schedule

I was not sure if the core of what you are asking for (#1) is an algorithm to satisfy your 4 rules. Then comes the "implementation" in Excel (#2), see above. For #1 the question is perhaps better suited for another SE site. I will answer this here, using numbers i instead of names for the people. Your requirements are here in a numbered listed.

  1. Each participant goes to the minimum number of meeting with each other participant. That is, Alpha does not have lots of meetings with Beta and none with Gamma, but has as far as possible an equal number of meetings with everybody.
  2. Each participant has one meeting a week, no more no less.
  3. There are two meetings of three people, and one of four people.
  4. Similar meetings are as spread out as they can be. That is, Gamma and Kappa are not in the same meeting for three weeks running.

I will defer the mathematical formulation of each requirement (Note: I started writing it, but I think it will add little here), and write the solution conceptually. I will only use the following:

  • Nij for the number of meetings i has with j (regardless of whether in meetings of 2, 3 or 4 people).
  • The total number of meetings per week for i is Mi=sum(j,Nij).
  • The total number of meeting-person per week is Q=sum(i,Mi).

With one meeting of four people (say, i=1-4), and two of three people (say, i=5-7 and i=8-10), per week, you are already satisfying rules 1, 2, 3. I will show how.

̲R̲u̲l̲e̲ ̲2̲: The absolute minimum for Q is 0 (trivial, no meetings at all, Mi=0). If you want each person to have exactly one meeting a week, Mi=1 and Q=10. So each person will have only one Mij=1 and all the others Mij=0.

̲R̲u̲l̲e̲ ̲3̲: The only way to satisfy this and Rule #1 is with one meeting of four people (say, i=1-4), and two of three people (say, i=5-7 and i=8-10). So the other rules are either simply compatible with #2 and #3, or make the solution not possible.

̲R̲u̲l̲e̲ ̲1̲: This seems an ill-stated rule. The best I can come up with is, taking the maximum number pi of meetings for i with any other, the minimum number qi of meetings for i with any other, and the difference ri=pi-qi among them, minimize all ri. Since we don't have a way of prioritizing the differences ri, in whichever optimization we would give equal weights to all ri. At any rate, with the unique solution we already have it is pi=1, qi=0, ri=1, for all people.

As for rule #4, it is not clear what do you call "the same meeting". If two meetings are the same only if they have exactly the same participants, with the proposal above you have plenty of combinations to choose from prior to repeating. For instance, you could rotate positions by one each week, so you would only be repeating the meetings once every 10 weeks.

This is a simple schedule that satisfies all of your requirements as you wrote them (and it doesn't need any Excel tinkering). I am not sure this is what you are really aiming for, but if not then I guess you did not reflect it properly in the specification. If that is the case, please post another question.

1

I have created ONE Month's schedule, accordingly you have to create for another months, what I've done, used below shown formula checks occurrence of participants, if has count not more than ONE.


enter image description here


N.B.

  • An array (CSE)Formula in cell K6, counts occurrence of participants.

    {=SUM(((LEN(C$6:E$6)-LEN(SUBSTITUTE(C$6:E$6,I6,""))) / LEN(I6))-((LEN(C$6:E$6)-LEN(SUBSTITUTE(C$6:E$6,"_I6",""))) / LEN("_I6")))}
    
  • Finish formula with Ctrl+Shift+Enter & fill down.

  • You need to change cell references in the formula, as needed for other weeks.

  • Cell E3 has start date for the month.

  • Cell B7 reads value from E3, and for other is + 7.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.