Solver Error

[Steve M]

Does anyone know if setting constraints with the SolverAdd command that
limits the "Changing Cells" to only positive numbers and also uses the
SolverOptions command with a AssumeNonNeg:= true causes the solver to use
negative numbers in the "Change cells"?

I have the following type of problem.

y1=a1*rate1 + b1*rate2


Constraints
0<=a1<=340
0<=b1<=340


y1 is a givens and I am attempting to minimize a1+b1. the values being
changed are a1 & b1,
In addition to setting the constraints for a1 & b1 as only positive number
by using the SolverAdd command I also use the SolverOptions AssumeNonNeg
:=True.
This example oversimplifies the problem in reality there are over 100 y's
and up to 13 terms in each equation. The solver returns correct results when
the rate1 and rate2 are different. However when they are the same the
solver will disreguard the non negative constraint and set one value to
negative.

I capture the solver completion codes and record it for each equation and
the solver returns 0 indicating convergence and constraints satisfied.

Any ideas?
Thanks

 

[Dana DeLouis]

Just a guess. In your simplified test example, let's assume rate1 is
greater than rate2.
The solution is to use as much of a1 as possible. If it reaches the limit
of 340, then the rest goes to b1.
When rate1 and rate2 are equal, then there are many possible solutions.
Solver may be aborting early when if detects there are multiple solutions.
We would have to investigate further if this is the case. Again, only a
guess at this point.

 

[Steve M]

I did consider that. However, the solver returns a completion code of 0
which I understands means, "Solver has converged to the current solution.
All constraints are satisfied." For the condition you suggest I would expect
a completion code of either 3 meaning " Stop chosen when the maximum
itterations limit was reached." or a code of 4 meaning " The set target cell
values do not converge." I know I am capturing the completion codes
correctly since I do get codes of 5 and 6 when appropriate. Also I can watch
the trial solutions and never see any that even come close to the maximum
itteration.

Very clearly the problem seems to only exist when the rate values are equal.
Also it appears that it will always maximize (positive) the value of a1 and
always minimize (negative) the value of b1.

Example:
If y1=a1*rate1 + b1* rate2
and rate1 = rate2 = 6329, and y1 = 69000
the solver returns a1 =340 and b1 = -329.1
the SolverOptions command is:
SolverOptions AssumeLinear:=False, AssumeNonNeg:=True, precision:=0.00001

I also have the solveradd commands set to constrain a1 and b1 to positive
numbers between 0 and 340.

This one has me at a loss.

 

[Dana DeLouis]

If you don't get a better reply, here's an observation...
Your problem is:

minimize a1+b1
subject to:
y1=a1*rate1 + b1* rate2
rate1 = rate2 = 6329
y1 = 69000
0<=a1<=340
0<=b1<=340

For Solver, I changed the first constraint to:
y1-(a1*rate1 + b1* rate2) = 0

I had different results depending on the starting values. The solutions
were definitely not optimal, and the return code sometimes jumped around.
My opinion is that Solver is not sophisticated enough to handle these types
of issues.
Here's what I was trying to mention earlier.
When we have an equality constraint, (y1=a1*rate1 + b1* rate2), we always
face the issue of accuracy.
In Solver's options, we deal with "Precision", "Tolerance", and
"Convergence." We also have Excel itself trying to determine if two numbers
are equal due to precision.
Such limits make y1=a1*rate1 + b1* rate2 "almost" never return "True."

So one technique is to try to eliminate any equality constraints.
Let's rearrange this equation to solve for b1. This doesn't really change
anything.

b1 = (y1 - a1*rate1)/rate2

Since b1 equals the above, and our Target is a1+b1,
let's change our target to include the above...

Minimize Target:
a1 + (y1 - a1*rate1)/rate2

Now we can eliminate that equality constraint.

Now, with our new Target, if rate2 equals rate1, then the objective reduces
to just:

y1/rate1

Your target is a constant y1/rate1.

Your Target cell doesn't make sense at this point. When Solver runs, every
time it makes a change, it has to work hard an setting a constant back.
Solver is just not sophisticated in my opinion to catch what is going on,
and will usually abort at the first sign of confusion.