LP solver problems

[MartenP]

I have some problems using the solver for solving a LP scheduling problem. It is a basic scheduling problem where I want to determine the number of servers to schedule per hour and to determine the number of different shifts. The number of scheduled servers per hour need to be equal or larger than the required number of servers (see attached excel file)
Scheduling problem.xlsx
I am fairly certain the model mathematically checks out, so I am assuming I made an error in entering it into excel. Solver indicates that the model is not lineair and therefore cannot find a solution, however the model should be lineair and when checking the model manually it easy to see there are schedules possible (for example: 3 agents starting at 06.00, 11 at 09.00 and 9 at 16.00). Does anyone have a clue what goes wrong here?

Just to be clear, the problem seems to be in the constraint on allowable number of shifts. I would like the solver to be able to adjust the solution to the number of shifts allowed. When I add these constraints the model becomes non-linear according to excel, using the non-linear method results does not help since it indicates that it can not find a feasible solution, although manually a solution can be found.

[Alf]

In you formula (cell E28) you are using "COUNTIF" but solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF" so you need to change formula.

Must say I really can't understand your constraint but as you set "Assume Non-Negative" in options you really don't need the constraint

Please Login or Register  to view this content.

.

Why solver tells your model is not linear I can't explain. My experience is that solver works in mysterious ways its miracles to perform!

I've added a link to Microsoft showing one way of setting up a workforce schedule.

http://office.microsoft.com/en-us/ex...001124598.aspx

Alf

[MartenP]

Thanks, that fixed the problem. I have rewritten the problem such that it is possible to toggle shift starts on and off (1/0), so now the solver is able the determine the optimal number of servers for an schedule I manually determine. However I would like the solver to determine the schedule for which the number of agents is minimized while still meeting the constraint in G31:I31 (maximum number of shifts).
I added again the excel file but I would like to have excel fill in column D8:D25 (binary values)

So does anyone have an idea how to do this?

Scheduling problem.xlsx

[rounakbhasin]

For the new problem using a non linear model is giving the solution. Just make the following changes in solver
By changing cells: D8:E25
and add a constraint
D8:D25 = Binary
Uncheck the assume linear check box in solver option. Solver will get a solution.

CLICK the STAR icon on the left side below their user name to say thank you to those who had helped you. Thanks.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.

[MartenP]

Thank you! It worked!