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 regular, but not exact. I want to also assign them time slots (1 of 3: morning, afternoon, and evening), and I want the time slots to be equally spread among each assessment date. For example, if September 2nd has 50 employees, then around 16 employees should have the morning slot, 17 the afternoon, and 17 the evening (i.e., 50/3=16.66667). I want the slots to appear in Column J, wherein the cell will read either "morning slot", "afternoon slot", or "evening slot".
Thanks!
- 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.- 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.
