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!!!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,507
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!
 

Forum statistics

Threads
1,140,944
Messages
5,703,305
Members
421,290
Latest member
james90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top