Optimal task time scheduling and assigment problem - Excel Solver

Your setup is a valid alternative, and I'm happy you could solve your problem. However, introducing a value for each person, you have added one layer of complexity.

The first rule in this kind of constraint problems is: keep it simple.

You can get the same result constraining directly the binary variables, as I suggested. I'm attaching a worksheet you can test, and that will work even if you add people.

HTH,

Francesco

Originally Posted by ciprian

Let's try and go a bit further and have not have a fixed time for the tasks in the begining.

Do you mean that time (for each task) is a variable, and its value should be somehow found by Solver? Then there are very few solutions if we must respect the time constraint of 40 days, and we don't need Solver.

First, task 5 cannot be > 40, so its value is within 35 and 40. No task is smaller than 5, so task 5 must be either 35 or 40 (otherwise we are left with a number < 40).

If it is the former, we could set Task1 (or 2) = 5, sum it to Task5 and reach 40, but then we are left with a Task3 (or 4) that is always < 40.

Otherwise, we can express that Task1 (or 2) + Task3 (or 4) = 40, and 5 < Task1 < 15. There are 11 couples of values that satisfy the equation and the constraint.

If I have misunderstood your question, please explain what may be another valid solution.

HTH,

Francesco

This example is just a water-down version of the actual problem which has 30 to 40 tasks each with a min/max possible duration that have to all fit in a calendar semester with 15 people assigned to them.

So getting a Excel Solver solution to the 5 tasks and 3 people example would help me extrapolate to resolve the actual problem.

I know that with a constraint of 40, Task 5 cannot be 45, but I want Excel Solver to come to this solution. I could have made the example with all 30+ tasks and people, but I thought it would be too much and having fewer tasks would be easier to understand the issue and the solution needed.