Solver

[Alonso]

Hi everybody

i'm trying that Solver finds the best way to schedule several machines

I have a table with 15 rows (4-18) the machines and 21 columns for the 7
days/3 shifts (B:V)

these would be the changing cells
1rs constraint: binary to know if the machine is on (1) or off (0) on that
shift

column W sums the row total, and I have on column X how many shifts the
machine should be working
2nd constraint: W=X

row 19 sums the column total, and it should be equal or less that the
available machines per shift, shown in row 20
3rd constraint: 19<=20

since it's better to group the working shifts per machine
one approach to solve this was to use a secondary mirrow table
where it would be adding the mirrowd cell to the one that is before
(ie. for cell B4, its mirrow cell its AB4
for B5, the mirrow cell is AB5 with value of B5+AB4, and so on)
at the end, sum each mirrow row and get a grand total
this would be the target cell and objective: maximize)

i need help to add constraints and/or modify the target cell cause right now
i only get the message:
"Too many adjustable cells"

 

[ShaneDevenshire]

Hi,

I think you need to look at Microsofts solver examples, to get an better
solution:

Most likely there is a file on your computer called SOLVSAMP.XLS which has
some very useful examples.

You can go to http://support.microsoft.com/kb/146197
and click the Sample Files link and it will show you where the samples can
be found on your computer. Personally, I would just do a Search for them.

If this helps, please click the Yes button.

 

[Mike Middleton]

Alonso -

15 rows times 21 columns = 315 cells

The standard Solver included with Excel has a limit of 200 adjustable cells.

To verify, search Excel Help for "specifications and limits."

For more powerful versions of Solver, browse to www.solver.com

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Alonso]

thanks Mike

i found what you said
guess i should try the other versions

 

[Dana DeLouis]

Just throwing this idea out...
Are you able to have 3-4 machines do the same schedule?
Perhaps combine a few of them into the same schedule.
In other words, one schedule with all 21 possibilities.
Then, an "Integer constraint" where that row's production is multiplied
by 0, 1, 2 or 3...etc.

If all the machines are the same, perhaps another technique would be to
dedicate a few machines to working only during the weekday, and be off
for the weekend (for maintenance, etc). This way, you could "x" out a
few of the changing weekend schedules. The goal being to get it down to
200 changing cells.

- - -
HTH
Dana DeLouis