C
callcenter101
I need to calculate staffing to keep 3 different sites busy with calls.
I know the staffing for site A, and am staffing site B and C based on site
A's understaffing. Site B and C need to receive a minimum of 9.5 calls per
hour, as well as receive the staffing proportion relative to their site size.
I now also need to make sure that site C receives the closest amount to 75%
of site A's calls. Here is what I have so far......
Cell B4 total call volume being handled
Cell L4 has the understaffing from Site A Listed
Cell M4 is the absolute value for L4
Cell P3 has the proportion that Site B can take from the understaffing at
Site A
For site B
P4=IF(L4<0,(ROUNDUP(M4*$P$3,0)+2),0)
R4=P4*4.75
For site C
Q4=IF(L4<0,(ROUNDUP(IF(M4-P4<0,0,M4-P4),0)+2),0)
S4=Q4*4.75
% of calls from Site B & C being handled
T4=(R4+S4)/B4
I need to determine an additional calulation that adjusts the formula in Q4
so that Site C receives as close to 75% of the calls as possible - the
calcuation needs to return the value that has the smallest difference but
NEVER more than 100%
Thanks
I know the staffing for site A, and am staffing site B and C based on site
A's understaffing. Site B and C need to receive a minimum of 9.5 calls per
hour, as well as receive the staffing proportion relative to their site size.
I now also need to make sure that site C receives the closest amount to 75%
of site A's calls. Here is what I have so far......
Cell B4 total call volume being handled
Cell L4 has the understaffing from Site A Listed
Cell M4 is the absolute value for L4
Cell P3 has the proportion that Site B can take from the understaffing at
Site A
For site B
P4=IF(L4<0,(ROUNDUP(M4*$P$3,0)+2),0)
R4=P4*4.75
For site C
Q4=IF(L4<0,(ROUNDUP(IF(M4-P4<0,0,M4-P4),0)+2),0)
S4=Q4*4.75
% of calls from Site B & C being handled
T4=(R4+S4)/B4
I need to determine an additional calulation that adjusts the formula in Q4
so that Site C receives as close to 75% of the calls as possible - the
calcuation needs to return the value that has the smallest difference but
NEVER more than 100%
Thanks