Dynamic Shift Rota Advise

Question

I am stuff on a problem regarding setting up a dynamic shift rota at work.

What it has to do is there are 3 people per team and 3 jobs to do (which they do as equals as it can be per week) and when someone is off lets call it job 3 is just not done that day.

I have been able to make it to set true or false if there in but having trouble with assigning a different job per day with priority on job numbers 1 and 2.

edit: I thought I would explain it better what I am looking for

This is the spreadsheet i have at the top is the rota 1 = in 0 = day off

I have only been working on Group 3 to get it working, In the grey next to the days at the bottom are the jobs in which that person would be doing if it was a full staff day.

What i need to do is if there are only 2 people in then they do job number 1 and 2 alternativly .

i currently have this formula it works for some combo but not all (this is formula for H33)

=IF(F9 > 0, IF(OR(F9=0,F10=0,F11 = 0),IF(OR(I33 = 1, I33 = 2),I33,I33-1),I33), 0)

Any advise woudl be great

Answer 1

Use this formula for John (column C row 4), and the copy it down to the other 2 people in the same day

  =IF(B4="in",COUNTIF(B4:B$4,"in"),"")

for next day you should change the B$4 to B$8, etc

EDIT / I did not undestand your rotation pattern

Now, refering to the excel image you posted, for Group 1, Monday

  You need two auxiliar columns

   1. In P11-> =D3*E23  // To eliminate zeroes

   2. Copy down P11 till P13

   3. In Q11->  =IF(P11=0,"",RANK(P11,P$11:P$13,-1)) // To get 1, 2 or 3 values

   4. Copy down Q11 till Q13

   5. In D23 -> =IFERROR(Q11-MIN(Q$11:Q$13)+1,"") // Offset Factor 1,1-2,1-3

   4. Copy down D23 till D25

It was a tricky one