Forum Discussion
Assign slots equally among employees with same assessment date
I have a sheet with employee data, and in column K, I've assigned them an assessment date. The employees are sorted by assessment date. The amount of employees per assessment date is more or less reg...
So I think you want to rank/order those assigned that day and then use
=CHOOSE(MOD(order#, 3)+1,"morning","afternoon","evening")&" slot"
so the first one would be #1 and the remainder after dividing by 3 is 1 so it would add 1 and show afternoon slot. and then #2 -> evening and then #3 (remainder is 0 + 1 = 1) shows morning and so on.
The question on how to rank/order them depends on if this is in a structured table or not and if you can rely on the list order or if there is another unique value you can use to order them by so the sorting order wont matter.
=CHOOSE(MOD(order#, 3)+1,"morning","afternoon","evening")&" slot"
so the first one would be #1 and the remainder after dividing by 3 is 1 so it would add 1 and show afternoon slot. and then #2 -> evening and then #3 (remainder is 0 + 1 = 1) shows morning and so on.
The question on how to rank/order them depends on if this is in a structured table or not and if you can rely on the list order or if there is another unique value you can use to order them by so the sorting order wont matter.
They are ranked in order of assessment date so the order should not be an issue. Is there a reference cell missing from this formula?
- "order#" must be calculated. you can use something like COUNTIF($A$1:$A2,$A2) if row 1 is a header row and row 2 is the first data row and column A is the assigned day. (adjust accordingly). you could also use something like =IF(A1=A2,B1+1,1) with same assumptions as above plus this formula starts in B2 and that those assignment days are at least grouped.