Calculate value closest to predetermined %

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top