target cells in solver

[jpr]

i need more than one target cell in the solver..... i have two variables that
have to merge to zero not be exactly zero....

 

[merjet]

The target cell must be a single cell. Try this. Use the ABS function
on the two cells you want near zero, then make the Solver target the
sum of those two cells and minimize it.

Hth,
Merjet

 

[SteveM]

The target cell must be a single cell. Try this. Use the ABS function
on the two cells you want near zero, then make theSolvertarget the
sum of those two cells and minimize it.

Hth,
Merjet

Don't do ABS unless you absolutely have to. It's non-linear and could
give you a ton of trouble. If you have two variables a and b that you
want to minimize, then make your objective function simply min a + b.
If a + b must be greater than 0, then you have to add a secondary
constraint, a + b => some small value (epsilon).

SteveM

 

[jpr]

that sounds good but the problem i found is that solver makesthe ABS value
zero or the two variables opposite...

 

[SteveM]

that sounds good but the problem i found is that solver makesthe ABS value
zero or the two variables opposite...

jpr,

Two things.

First of all, I assumed that your variables are bounded to be
nonnegative. If that is your intent then you have to bound them
greater >= 0.

And if the optimal solution is still 0,0 then you obviously don't have
any constraints in your formulation that prevent that from happening.
So there may be a flaw in your constraint set.

If you actually have a preference for one variable being closer to
zero then the other, then you may have to employ a Goal Programming
technique. You can find out online how those models are formulated.

And the second thing is that you can circumvent the mess of an
absolute value function by formulating it as mixed integer problem
(MIP).

Here's a cut-and-paste from a webpage that describes it:

There are two alternatives:

f(x) >= 0 that gives f(x) >= m

f(x) < 0 that gives -f(x) >= m or, eqiuvalently, f(x) <= -m.

Let z be a binary variable such that:

z = 1 means m <= f(x) <= +M

z = 0 means -M <= f(x) <= -m

where M is a "big" constant. Then the constraint can be formulated
as follows:

m * z - M * (1 - z) <= f(x) <= M * z - m * (1 - z)

That looks kind of complicated. But it's not really. And sometimes
you have no choice. Because an absolute value function requires the
use of non-linear programming techniques which limit the size of the
problem you cam solve. In fact the non-linear solver built into Excel
really stinks.

SteveM