Using the Solver function to calculate a schedule.

[USAF]

I have "tinkered" with this spreadsheet and watched more than a few online videos trying to get Solver to make a schedule using the goal and constraints needed. While I can get it to calculate an answer, it is not doing so efficiently. The goal should be the minimum (highlighted in orange), based on the actual number of personnel scheduled. Instead, it seems to want to focus on spreading the hours out evenly among whoever is available for the day.

The first tab is the kind of results I am hoping for and the next tabs are the different results I have gotten depending on the value I start with in the data cells.

Any guidance would be greatly appreciated. I feel like I must be missing a step.

Thank you,

Michael

[Hydraulics]

There are good news and bad news.

The good news is that your problem can be solved to optimality (best solution is 21).

The bad news is that you have to add a new set of (binary) variables, and Solver will complain that the problem is too complex. Therefore you need to install OpenSolver, a free and open source add-in.

The problem lies in your COUNTIF() function in row 22, that is non-linear. We can use a workaround, defining a new set of vars for each employee and day of the week. If we can force these vars to switch to one when the corresponding integer var is greater than zero, we could use a simple SUM() function, which is linear, and then minimize their sum. In the attached file you'll find a very simple setup.

HTH,

Francesco

[USAF]

Francesco,

I appreciate you for taking a look at this. I will be on the lookout for non-linear functions in the future. I look forward to trying out OpenSolver. I was afraid the problem called for too many renditions of possible solutions to calculate in the original form.

Thank you,

Michael

[USAF]

Francesco,

I played around with it some more and was able to make the Solver table linear and use a helper table to calculate the hours. I added another constraint for the number of days in the week to schedule an employee in order to distribute the workload more evenly. Again, thanks for the tip. I am reading up on OpenSolver.

Michael

[Hydraulics]

Michael,

your problem is still non-linear. Solver will gladly use the Simplex LP because it gets fooled by references to other sheets, but OpenSolver, which is a little more reliable, will warn you.

There's nothing wrong in using GRG or Evolutionary engines, just remember that you have no guarantee that the solution you find is the best one.

Any function like IF(), COUNTIF(), SUMIF(), LOOKUP() and the like that involves variables is going to make your problem non-linear. I can't see how to solve this schedule without a second set of variables, but maybe someone here will help you further.

HTH,

Francesco

[USAF]

Appreciate the explanation. I understand this is a "workaround" of sorts. It at least gets me closer to what I'm trying to achieve and is usable for my needs. Thanks again.