joeu2004
Tue May 08 12:41:42 CDT 2007
On May 8, 4:41 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> The developers of Solver (Frontline Systems ) clearly state in many places
> that the simple Solver in Excel (and QuattroPro) cannot handle discontinuous
> expression such as those with IF, Vlookup, etc. Visit www.solver.com.
Thanks for the pointer. But can you provide some URLs for the "many
places" where that limitation is mentioned?
What I do find at www.solver.com are many statements to the effect
that such "nonsmooth nonlinear" characteristics of a model make the
problem "hard", with solutions that might be only "locally optimal" or
not optimal at all. Here is one quote (emphasis added) from
http://www.solver.com/tutorial6.htm :
"Nonsmooth optimization problems -- where the relationships may
include functions like IF, CHOOSE, LOOKUP and the like -- __can_be__
solved up to scores, and occasionally up to hundreds of variables and
constraints, given enough memory and time. You can only have
confidence that the solutions obtained are "good" (i.e. better than
many alternative solutions) -- they are not guaranteed to be globally
or even locally optimal."
Note that it says that Frontline's Solver __can__ solve such problems,
at least sometimes, in contrast to your blanket "cannot" assertion.
On the other hand, it is true that Frontier does also make the
following statement (on the same web page; emphasis added):
"A model that consists of mostly linear relationships but a few
nonlinear relationships generally must be solved with more 'expensive'
nonlinear optimization methods. The same is true of models with
mostly linear or smooth nonlinear relationships, but a few nonsmooth
relationships. Hence, a single IF or CHOOSE function that depends on
the variables __can__ turn a simple linear model into an extremely
difficult or even unsolvable nonsmooth model."
Note that it says that it __can__ (might) be unsolvable, but not
necessarily so. Moreover, I hasten to point out that I used the
default Solver options, which deselect the Assume Linear Model
option. I ass-u-me that means that Solver uses the "more expensive
nonlinear optimization method", which is needed to solve such
"nonsmooth" models, if they can be solved at all, according to the
Frontline web page.
That said, I do agree that you have offered a __potential__
explanation of the behavior. Without detailed knowledge of
Frontline's algorithms, it is hard to say. I find it surprising that
Solver works fine when the target cell is empty, but it finds "no
solution" (i.e. a solution that violates the constraint) when the
target cell has certain values.
Perhaps the "right" answer for my example is: Solver is taking the
value in the target cell as a "guess" and progressing from there. If
that is not the user's intention (it was not mine; I did not select
the Guess option), perhaps it is best to leave the target cell empty.
(But presumably, offering a guess somehow might, in fact, improve
Solver's result sometimes.)
My greater concern is that Solver does not offer any indication of the
"quality" of its result. In this case, I think it should have warned
the user that the result is "no solution" at all, since it violates
the constraint.
Bottom line: bugs or not, I think the user should be very cautious
about relying on results from Solver. It behooves the user to study
the result carefully to be sure that it is a solution at all. Even if
it is, the user should not assume that it is optimal, even locally.
(Sigh!)