Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver
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" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver
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. -- Thanks, Shane Devenshire "Alonso" wrote: 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" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver
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" wrote in message ... 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" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver
thanks Mike
i found what you said guess i should try the other versions "Mike Middleton" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Solver
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 Alonso wrote: thanks Mike i found what you said guess i should try the other versions "Mike Middleton" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to add-in solver without cd | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Solver | Excel Discussion (Misc queries) | |||
Solver help | Excel Worksheet Functions | |||
solver | Excel Discussion (Misc queries) |