excel solver constraints

Hi all,

Good day!

I am currently facing some difficulties in constructing the constraints for my solver model.

Referring to the sample attached, I have to make an schedule for employees to attend training sessions, 2 times per month.

I have 2 constraints that I am not very sure on how to add in:
1) 2 sessions have to be at least 2 weeks apart. It doesn't have to be exactly 14 days. For example, if 1st session is on 1st week, 2nd session must be at least on 3rd week, anyday.

2) The sessions must be scheduled on the day the are scheduled to work, as shown in the second table in my sample file.

Hope that my request is clear.
Appreciate any assistance!

Thank you!!

The simple problem first

sessions must be scheduled on the day the are scheduled to work

I've set up a result grid i.e. multiplying solver binaries with the appropriate workday see example in range C20:AG22

Your other problem is a bit more tricky but I'll have a go at it and see if I can solve it.

Alf

Hi Alf,

Thank you for your effort.

On top your work, I added a another constraint using contif. The result generated is able to show 2 sessions on different week, however:
1) Still haven't figured out how to have them at least 2 weeks apart - am thinking the logic would be another countif to capture continuous cell above zero using =frequency or something else.... not sure how...
2) Solving method have to use GRC instead of simple linear (don't think is an issue though)

Also, I found that by adding that one additional constraint, the calculation will take few seconds longer... can't imagine how long it will take when I apply them to my whole team which is around 20+ people

I think the newly added constraints have to be improved....

Had a look at your new setup and tested it a bit but could not improve the time difference. Also had a go at using the "Evolutionary" solver but this was a "no go". After some time solver gave up i.e. did not find a setting that satisfies all constraints conclusion is that for this setup the "GRG Non-lineare" is the best setting.

So I got back to my uploaded file and cheated, I just removed some of the formulas in in range C20:AG22 see result on my cheat sheet. One advantage using this technique is the the model is still linear so the one gets the result quite faste.

There probably is a constraint setting that will give you the desired result but I have no idea at the moment how to formulate that.

Alf

Hi Alf,

Thank you so much for your effort.

At least it save me a couple of steps for the scheduling though...

I'll keep working on the constraints setting. However, there are some circumstances that the solver does not "react" to the constraints. Maybe the constraints are too complicated..

Will continue to explore from here though.
Thank you.

You are welcome.

Maybe the constraints are too complicated

If solver stopps saying "Solver can not find for which all constraints are fulfilled” then this could be the case but if the contraints are ignored without any error message I would suggest you check the logic for the constraints solver ignore to see if they are valid.

You could also check with the solver help desk:

http://www.solver.com/

Alf