Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate value closest to predetermined %
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance to predetermined cell | Excel Worksheet Functions | |||
Advance to predetermined cell | Excel Worksheet Functions | |||
Advance to predetermined cell | Excel Worksheet Functions | |||
Advance to predetermined cell | Excel Worksheet Functions | |||
Calculate the closest day | Excel Worksheet Functions |