Solver Maximum adjustable cells & Problem with Binary cells

[yael]

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

[SteveM]

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