Hello all, This is my first post but I've been browsing this forum for years. I'm a tech savvy guy with some mathematical and statistical background but I don't use excel enough to know exactly what to do in my current situation.
I developed a calculator of sorts that supervisors at my company can use to see the impact on the rest of the workforce when they let their workers take a day off. See the attached sample sheet that the supervisors can check a box if the worker is here that day. Then the sheet calculates the capacity of tasks our entire workforce can complete in one day based on who is here and based on the given worker's capability. Not every worker can complete the same number of tasks but all tasks have to be completed in the same day.
I also have just a basic mean average calculator that will show the number of tasks every worker would get if you enter an estimated value. However that doesn't account for the workers who are new and aren't at full capacity. Plus on busy days the workers that are at full capacity (6-8) may have to take more tasks than they are supposed to be capable of (more than 8 tasks).
I would like to set up a column that will show how many tasks a worker will receive if they are present given the estimated number of tasks entered into the estimated tasks box, but they should not exceed the capacity unless that capacity shows "6" or "8", then technically that worker can receive more. Also, the estimated number of tasks cannot be allocated to workers who are not available.
I want it to divide up evenly but the workers on limited rotation would get the same number as everyone else until they reach their limit. For example, there are 109 workers; if the estimated tasks are 218, each worker should receive 2. If the estimated tasks are 436
then every worker would need to receive 4 except for the workers who can only receive 1, 2 or 3 tasks. Then remaining tasks that they could not complete would have to be assigned to other workers that are at full capacity, even if they go over their limit.
A smaller scale example might be easier to understand: if I had one worker who could receive 4 tasks, one who could receive 5, one who is at full capacity of 8, and one worker absent for the day. The overall capacity should be 17 tasks. However, if 19 tasks are received in a day to complete, the worker who is at full capacity would have to complete 10. I would need the spreadsheet to show that the worker at 4 capacity received 4, the worker at 5 capacity received 5 and the worker at 8 capacity received the remainder, which is 10 in the above example. Then the absent worker would show 0
Worker____Capacity___Present?_____Estimated # of tasks:
Bob________8________Here_________10
Joe________5________Here__________5
Sue________6________Out___________0
Jane_______4________Here__________4
Estimated total tasks for the day: 19
In this example, I need a formula to calculate D2:D5 based on B6 (19).
I've tried every combination of arrays, allocations, ranks and distributions I can think of and I can't seem to get it to do what I want it to do. Any help or anyone who can point me in the right direction would be greatly appreciated.
I developed a calculator of sorts that supervisors at my company can use to see the impact on the rest of the workforce when they let their workers take a day off. See the attached sample sheet that the supervisors can check a box if the worker is here that day. Then the sheet calculates the capacity of tasks our entire workforce can complete in one day based on who is here and based on the given worker's capability. Not every worker can complete the same number of tasks but all tasks have to be completed in the same day.
I also have just a basic mean average calculator that will show the number of tasks every worker would get if you enter an estimated value. However that doesn't account for the workers who are new and aren't at full capacity. Plus on busy days the workers that are at full capacity (6-8) may have to take more tasks than they are supposed to be capable of (more than 8 tasks).
I would like to set up a column that will show how many tasks a worker will receive if they are present given the estimated number of tasks entered into the estimated tasks box, but they should not exceed the capacity unless that capacity shows "6" or "8", then technically that worker can receive more. Also, the estimated number of tasks cannot be allocated to workers who are not available.
I want it to divide up evenly but the workers on limited rotation would get the same number as everyone else until they reach their limit. For example, there are 109 workers; if the estimated tasks are 218, each worker should receive 2. If the estimated tasks are 436
then every worker would need to receive 4 except for the workers who can only receive 1, 2 or 3 tasks. Then remaining tasks that they could not complete would have to be assigned to other workers that are at full capacity, even if they go over their limit.
A smaller scale example might be easier to understand: if I had one worker who could receive 4 tasks, one who could receive 5, one who is at full capacity of 8, and one worker absent for the day. The overall capacity should be 17 tasks. However, if 19 tasks are received in a day to complete, the worker who is at full capacity would have to complete 10. I would need the spreadsheet to show that the worker at 4 capacity received 4, the worker at 5 capacity received 5 and the worker at 8 capacity received the remainder, which is 10 in the above example. Then the absent worker would show 0
Worker____Capacity___Present?_____Estimated # of tasks:
Bob________8________Here_________10
Joe________5________Here__________5
Sue________6________Out___________0
Jane_______4________Here__________4
Estimated total tasks for the day: 19
In this example, I need a formula to calculate D2:D5 based on B6 (19).
I've tried every combination of arrays, allocations, ranks and distributions I can think of and I can't seem to get it to do what I want it to do. Any help or anyone who can point me in the right direction would be greatly appreciated.
