solver cannot take "if "function?

[MIchael]

Hello
I use solver to calculate the minimum with the following equation
x1+n1*y1+o1*z1+x2+n2*y2+o2*z2=Min!

shipping costs should only be included of the shop is used!, therefore a if-the function is used:
Cell x1(and x2) contain the function if(or(n1>0;o2>0;10;0) thats adds the shipping costs.
But this is ignored by solver, as if the if equation is done after the solver calculation.
Optimal would be the following (500Euro):

x n y o
z

shipping count(var) PriceWine count (var) PriceH2O

shop1 10 4 120 1 10

shop2 0 0 120 0
11



but Solver calculates (510Euro):


x n y o
z

shipping count(var) PriceWine count(var) PriceH2O

shop1 10 2 120 1 10

shop2 10 2 120 0
11


(constraints are; integer; >0; n1+n2=4; o1+o2=1)

Any idea why and any workaround available?
Thanks for the help!
MIchael

 

[Dana DeLouis]

shipping costs should only be included of the shop is used!...

... therefore a if-the function is used:

Hi. In general, Solver can not use IF functions. Solver can not keep track
of why values jump for small input changes.
I can't follow your example below, so I'll write this as a general idea.
I think you have a fixed cost of $10 if you produce any item at all, and $0
if not used.
The technique is to make your model more linear. I'll use:
x - Variable Amount
C - Costs
FC- Fixed Costs.
B - Binary Constraint (0/1)

In general, your Target cell includes fixed costs:
x*C + FC * B

With the constraints:
x <= B * 10000
B = Binary Constraint.

The 10000 is an reasonable upper limit of production of x. We want it to be
larger than the solution, but we don't want to make it too large where
"Scaling" of the problem becomes an issue. (largest values relative to
smaller values).
Now, Solver will work, as it can keep track if it makes anything (B=1), or
nothing (B=0)

--
HTH :>)
Dana DeLouis
Windows XP & Office 2007

 

[MIchael]

Hello Dana,
thank you very much for your answer but the case when theres no Production (x=0), the binary constraint is not set to 0 because
x <= B * 10000 is true that means : 0 <= 1 * 10000 is true
did I miss something?
Bye
Best wishes
MIchael

 

[MIchael]

Hi Dana,
I created it once more from scratch and it runs!!
I do not believe it
And I really dont completely understand why ;.-))
Thanks again!!!
Michael

 

[Dana DeLouis]

Great! Glad it is working, and thanks for the feedback.
I’ll try to answer your other question:

“When there’s no Production (x=0), the binary constraint is not set to 0
because x <= B * 10000 is true that means: 0 <= 1 * 10000 is true
Did I miss something?"

Yes, you are correct, but the reason is a little hard to see at first.
When doing “fixed costs” problems, let us first assume Solver sets the
binary to 1 as above. This constraint allows us to set x up to 10000.
However, more importantly, it adds the $10 Fixed cost to the target cell.
It is quite possible that Solver will move x towards 0 as in your question.
That’s fine, but the Target cells still has the $10 fixed costs. Therefore,
for a moment, we have the situation as in your question. The next step for
Solver will be to realize that it can minimize the Target cell more by just
setting B to 0, thus removing the fixed cost.
Hope that helps. :>)