Re: target cells in solver
- From: SteveM <sbmack7@xxxxxxxxxxx>
- Date: Tue, 17 Jun 2008 14:49:15 -0700 (PDT)
On Jun 17, 3:50 pm, jpr <j...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
that sounds good but the problem i found is that solver makesthe ABS value
zero or the two variables opposite...
"SteveM" wrote:
On Jun 16, 4:29 pm, merjet <mer...@xxxxxxxxxxx> wrote:
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,
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
.
- References:
- target cells in solver
- From: jpr
- Re: target cells in solver
- From: merjet
- Re: target cells in solver
- From: SteveM
- target cells in solver
- Prev by Date: Concatenate the lookup values from all occurences
- Next by Date: Re: How to programmatically set ControlSource?
- Previous by thread: Re: target cells in solver
- Next by thread: what is sumproducts cell limit?
- Index(es):
Relevant Pages
|
