Is this even Possible???

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
Hi All,

I've got a REALLY tricky problem to solve.... (Please help me all you Geniuses!)

I have a profile of the day, broken down into 15 minute segments. In each of these segments is a number representing the number of additional agents I need to take telephone calls (G20:AX20). The list looks something like...

4 4 0 3 4 5 3 5 0 1 1 0 0 2 1 4 3 1 2 0 ..........

which might equal 60 in total for the whole day, which would represent 15 hours as each is a 15 minute slot.

So, If I had an additional 15 hours available I could schedule my 15 'overflow' agents to take the calls accordingly.

HOWEVER, The problem is that each agent can only do a minimum of 30 minutes in one go and up to 2 hours maximum each time (45 minutes WOULD be acceptable).

How can I obtain a profile from the above line of numbers, that fit in the best possible way - it doesn't have to be exact, as it is not always going to be possible - with the min 30 mins so that I could then go away and assign shifts???

E.G. I would want from the above something like...

(Original)
4 4 0 3 4 5 3 5 0 1 1 0 0 2 1 4 3 1 2 0 ..........

(Desirable)

4 4 0 3 3 5 5 5 0 1 1 0 0 1 1 3 3 1 1 0 ..........

Or something similar... Hope this all makes sense.

Thanks HUGELY in advance for any help!!!!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,824
Office Version
  1. 365
Platform
  1. Windows
cob98tp

I can't see what the logic is to get from the 'Original' to the 'Desirable'?
 

cob98tp

Board Regular
Joined
Nov 18, 2004
Messages
146
That's the problem.. there is no right answer everytime.

I need to be able to fit 30 minute shifts to the 15 minute breakdown. I.E. there can be no odd 15 minute assignments anywhere. There CAN be 45 minutes, or longer (up to 2 hours) but no individual assignments.

Maybe I'd need to look at it from the 15 overflow agents themselves.

E.G.

4 4 0 3 3 5 5 5 0 1 1 0 0 1 1 3 3 1 1 0 ..........

might consist of

1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ......... Agent 1
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 2
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ......... Agent 3
1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 4
0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 5
0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 6
0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 7
0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 8
0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 ..........Agent 9

and so on... Thus coming up with the above line?!? But without going over the 15 hours (or 60, 15 minute slots) but this way would then have to consider fairness of distributing the shifts.

All very complicated!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,656
Members
410,627
Latest member
georgealice
Top