Solver Ignores Constraints

[Guest]

Does anyone know exactly how/when Solver uses the parameter constraints?
When I ask Solver to find the value of x for which f(x)=0 subject to the
constraints that x>=L and x<=U, I always assumed that Solver would never try
to evaluate f(x) at values of x outside of the interval (L,U), but that
assumption turns out to be false!

For example, with
A1 = -1928199651581/72909355965555
B1 = -1442943051207/607577966379625
C1 = -43172037099106/5468201697416620
A2 = 5E-12
B2 = (A$1+B$1*LOG(A2))/(1+C$1*LOG(A2))
When I ask Solver to set B2 equal to zero by changing A2 subject to the
constraints that A2<=1E-11 and A2>=1E-12, then Solver encounters an error and
terminates
with A2 = -0.000000099995. Why was Solver even considering negative values
of A2?

Note that my question is about the operation of Solver with constraints, not
how
to find the root of this particular function. If I parameterize B2 as a
function of
LOG(A2) instead of a function of A2, then Solver will converge to a solution
with this function, but my concern is how to have constraints respected in
more complicated situations.

My testing was specifically in Excel 2003. I have not been able to get
Solver to install properly in the 2007 demo, possibly because I had earlier
run 2007 beta on that machine, though I did uninstall the beta before
installing the production demo.

Jerry

 

[Dana DeLouis]

Hi Jerry. I'm not an expert, but here's my thoughts.
You are asking for an A2 value within a very narrow range. This narrow
range is also right up against an error wall. I.E. with A2 <= 0, then
Log(A2) triggers an error.
I'm not sure, but I think there are a few things happening at once. Solver
usually doesn't make small step sizes of 1E-12. You will see these options
under Solver's options for "Precision" and "Convergence." You should
probably adjust these options. Solver "probably" does not start out with
such small steps. So, Solver's first step may have been in the negative
direction, and put A2 < 0
Secondly, Solver uses a form of derivative to establish its next guess. I
believe for this to work at the start, it needs a larger step size for a
decent derivative. .It's delta x may be larger than what you need, and
hence put A2 < =0, and hence an error
I tried changing the option for search from Newton to Conjugate, but that
didn't solve the problem here.

So, in other words, the solution is very close to zero. When we subtract
things like Precision, Convergence, and an unknown that Solver needs to do a
derivative, this seems to put the value <0. Hence the error. Solver does
not have enough logic to recover from this.

For this problem, we now need a technique that does not trigger an error
when A2 temporarily goes below 0. We can not use an IF() function.
Here is the equation:

= (A1+B1*LOG(A2))/(1+C1*LOG(A2))

Here's one technique.

Change LOG(A2), to point to a cell with this instead:

=-IMABS(IMLOG10(A2))

When A2 goes below 0, it's a complex number. The above will not trigger an
error.

I got a very good solution doing it this way.

We can compare Solver's solution to the exact solution:
=POWER(10,-A1/B1)