Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Maximum adjustable cells & Problem with Binary cells
Hi,
I have two questions: ------------------------- 1.What is the MAX number for the adjustable cells in the Excel Solver? 2.Problem with binary cells: The adjustable cells are arrenged in 2 matrixs: for each product, the solver should detrmine the manafacturing quantity for 12 months and should detrmine if to do setup to the machines (binary). The Target Function directly depands on these two factors : manfacturing quantity and setup cost. Two matrixs as follows: matrix1 is for integer numbers only - for the manufacturing quantities matrix2 is for binary (1/0)- for setup costs Now matrix2's cells depend on matrix1's cells that means that if the matrix1's cell value 0 then the cell in the marix2 should be 1 else 0. (If you manfacture so you should do setup to the machine) It means that I add in the Solver "subject to" constraint as follows: matrix1<10000000*matrix2. The problem is that the solver returns non-binary values for matrix2, why??? Should I change to different problem and matrix2 shouldn't be adustable cells and will include this fomula : IF ( matrix1_cell<0,1,0) ? can I do it ? If you need more info. to understand the problem I can send you the file and then you will be able to understand the problem clearly. Thanks, yael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver Maximum adjustable cells & Problem with Binary cells
Couple of observations:
Your "Big M" coefficient, 10,000,000 is probably too large, making the problem poorly scaled. Numerical instability may make the problem appear to be infeasible or unbounded. Reduce the coefficient to the order of magnitude of the largest value of the production variables. I have the upgraded version of the Solver, but if your version has an auto-scaling switch, turn it on. Consider using continuous variables for your production quantities and rounding the solution if you are producing more than a few of each item. You can't use conditional If statements using the linear solver. It assumes the If's make the problem non-linear. SteveM yael wrote: Hi, I have two questions: ------------------------- 1.What is the MAX number for the adjustable cells in the Excel Solver? 2.Problem with binary cells: The adjustable cells are arrenged in 2 matrixs: for each product, the solver should detrmine the manafacturing quantity for 12 months and should detrmine if to do setup to the machines (binary). The Target Function directly depands on these two factors : manfacturing quantity and setup cost. Two matrixs as follows: matrix1 is for integer numbers only - for the manufacturing quantities matrix2 is for binary (1/0)- for setup costs Now matrix2's cells depend on matrix1's cells that means that if the matrix1's cell value 0 then the cell in the marix2 should be 1 else 0. (If you manfacture so you should do setup to the machine) It means that I add in the Solver "subject to" constraint as follows: matrix1<10000000*matrix2. The problem is that the solver returns non-binary values for matrix2, why??? Should I change to different problem and matrix2 shouldn't be adustable cells and will include this fomula : IF ( matrix1_cell<0,1,0) ? can I do it ? If you need more info. to understand the problem I can send you the file and then you will be able to understand the problem clearly. Thanks, yael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does error message "too many adjustable cells" mean in solver | Excel Discussion (Misc queries) | |||
Adjustable Cells (Changing Cells) Limit - Solver | Excel Discussion (Misc queries) | |||
Solver Binary Contraints problem | Excel Worksheet Functions | |||
Solver returns non binary answer in binary constrained cells | Excel Worksheet Functions | |||
Excel 97 stuck in calculation loop - maximum cells problem? | Excel Programming |