Distributing work items when the head count is less
I have an existing work allocator which allocates 82 sites among working employees. However, when there is a shrinkage and there are less employees working only 60-75 work items are allocated.
Is there a formula to distribute the sites among the employees completely i.e 82 sites.
Attaching my existing worksheet for reference.
I don't believe that changing the formula is going to help here.
If I understand the file correctly under the 1-46 work items header, there are 25 work items assigned to employees in the CST, MST and PST. There are only 5 employees working and they are only allocated 4 work items. So unless those employees are allocated 5 work items or some of the work items designated for those time zones are reassigned to EST employees, the math isn't going to work.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
work items will remain the same, on special days like festivals / holidays the people working are less and it's not able to allocate all work items to the present people.
Is it possible to allocate everything leaving nothing behind, say there are 82 work items, and only 4 people are working, it's not allcoating 20-21 sites each.
If you add more columns then I will be able to allocate more work items. In the attached file column L is inserted, formulas copied over from column K, and now all of the work items are assigned. If you anticipate that there will be occasions where only four employees will be present then insert two more columns.
Let us know if you have any questions.
The problem with adding more columns in when the headcount is normal more sites will be allocated to the top rows because of more columns and the distribution won't be equal.
I want the distribution to be equal but the distribution here is column wise and not equal.
Using the file attached to post #4 change cells G16:G17 to 'working'. The distribution of agents 15:28 (some assigned 4 and others 3) is similar to that of agents 29:46 in the file attached to post #1 (some assigned 3 and others 2).
That's the problem, I don't want to change G16:G17 to 'working' and still allocate workitems to other people equally.Originally Posted by JeteMc
Bumpppppppp
It may be that if you manually show us what you expect on special days, and what you expect on regular days, that someone could come up with formulas/code that would replicate those results.
It doesn't depend on special days. Let me explain.
We have 40 people working in a team and daily team head count varies from 25-30 on average.
On some day might be because of anything, headcount is 11, this sheet fails to distribute all sites, if I add columns to increase the cells distributed it will result in unequal distribution. people in top cells will be allocated more sites compared to people on lower cells as the formula distributes column wise.
I am assuming that "sites" corresponds to "work items" in the file.
My understanding of the way the file works is that agents 15:28 are assigned to sites in the CST, MST and PST while agents 29:46 are assigned sites in the EST. Is that the way that you want the sites assigned, or do you just want the 46 sites to be evenly distributed to all agents that are working regardless of time zone designation?
Whatever you said is correct, only thing I want the work items to be evenly distributed in 2 groups EST, CST - 1 group [11 - 8 shift]
MST, PST - 2nd group [2 - 11 shift]
I don't believe that is going to do what you want as illustrated in the attached modified copy of the file that is attached to post #1.
The workbook is set up so that work items in the EST and CST are assigned to agents working from 11 to 8 and work items in the MST and PST are assigned to agents working from 2 to 11.
Note that work items listed in D33:D36 (Timezone sheet) are not assigned to any of the 8 agents working from 11 to 8.
I feel that the simplest way to get an equal distribution is to distribute the 46 work items without regard for time zone.
If time zone must be taken into account then the ratio of agents working from 11 to 8 to those working from 2 to 11 would need to be in proportion to the work items in the EST:CST to those in the MST:PST zones. I hope that last sentence makes sense.
Let us know if you have any questions.
the ratio of agents working is not in our hands sometimes and that's why there's a need to allocate the items equally among employees.
Worksheet attached in post #13 is still not useful because cells F53:F54 should be equal irrespective of the ratio of agents.
Total work items should be equal to Allocated work items.
The workbook is currently able to allocate only 79/82 for the given ratio of agents available on that day.
Bump...................
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks