Optimal task time scheduling and assigment problem - Excel Solver

I'm trying to resolve an assigment problem using Excel solver.

I have a number of task that each takes a number of days to complete.
I have a number of person to do the tasks. Each takes the same amount of time to resolve the tasks.

There are the following constraints:
- each task has to have 2 persons assignet to it
- each person has to be assigned a total time of 40 days

In theory the solution is something like this:
- Persons 1 and 2 to work on Tasks 1 and 3
- Persons 3 and 4 to work on Tasks 2 and 4
- Persons 5 and 6 to work on Task 5

I want to add an extra constraint regarding the Persons and how to assign them together. There are 3 types of Persons: Leader, member and newbie. The constraints would be:
- two Leaders cannot work on the same task
- two newbies cannot work on the same task.
All other combinations are possible: Leader and member, Leader and newbie, member and member, member and newbie.

Extra: for the Time Assigned range is there a better way to write the sum function?

This part is already solved, please take a look at post #6. I'm looking for a solution to optimal schedule the tasks as the first step before the people assigment.

Assuming Leaders are Person1 and Person2, you need to calculate the sum of those binary variables and constraint it to be <= 1, for each Task, e.g.

SUM(C13:C14) <= 1.

Same reasoning goes for newbies.

Extra: yes, there is. In M13

=SUMPRODUCT(C4:G4,C13:G13)

HTH,

Francesco

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.

I already outlined a model setup: express the relationships among Tasks as equations, and apply the constraints as min/max limits. You would need to switch to Evolutionary engine, since the problem is now non-linear.

More importantly, Solver has a limit of 200 on the number of decision variables, and we have approximately 525 binary vars (35 Tasks x 15 Persons) and 35 integer vars. Usually, extrapolation doesn't work very well in this kind of problems.

However, it may be possible to linearize the model and then use OpenSolver, a free Add-In that has no limits on the number of variables.

HTH,

Francesco