Solver for Nonlinear Programming [BETA]by Sun Microsystems, Inc. By default Calc ships with a solver engine for linear programming only. This allows the optimization of models to a certain degree. Currently it incorporates two Evolutionary Algorithms which are able to handle floating point and integer variables as well as nonlinear constraints. For more information about their usage and restrictions, please refer to the documentation. Both algorithms include work from Xiao-Feng Xie, who did a lot of research in this area. The extension requires at least StarOffice 9 or OpenOffice.org 3.0. License: opensource | Read license Source code: Browse source code Further product information: Product details Provider: Sun Microsystems, Inc. 
|
||
Report abuse here
By any use of this Website, you agree to be bound by these Policies and Terms of Use
Sun, Sun Microsystems, the Sun logo, Java, Solaris, StarOffice are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.
Comments
Yes, that is exactly what I
Yes, that is exactly what I mean. The solver only checks if the left-hand of a constraint matches one (or more) of the variable-cell-ranges. It then checks if the right-hand is a number or if the cell it refers to only contains a number (and not a formula). In that case it is interpreted as variable-bound, otherwise it's treated as normal constraint.
Theoretically it's also enough to have constraints only, but practically the bounds are necessary to allow a faster "startup" (since the bounds decide about the range, random numbers are generated from ... and if that range is essentially [-infinity to +infinity] then the solver takes ages until it pinned down a viable search region).
Post-optimal analysis
Great job. A big step ahead would be to make it possible to develop a post optimal analysis, as Frontline System's Solver does, because this feature is basic for a professional use of the extension.
Mariano A. Vizcaíno García
Its a start, but it seems very beta
I tried using it with OO 3.01 on ubuntu jaunty and got the "(com.sun.star.registry.CannotRegisterImplementationException)
{ { Message = "", Context = (com.sun.star.uno.XInterface) @0 } }" error.
I also tried it with OO 3.1 (portable version from http://portableapps.com/) on windows XP and then it works fine.
To test the solver I tried some textbook examples with mixed succes. For example:
Maximize: x1 - x2
subject to: -x2^2 + x2 = 1
no reasonable solution was found (should be x1 = 0.5 and x2 = 1.25)
secondly a quadratic programming example
Min: (x1 - 6)^2 + (x2 - 8)^2
s.t.: x1 <= 7
x2 <= 5
x1 + 2*x2 <= 12
x1 + x2 <= 9
x1, x2 >=0
no good solution was found (shoud be x1 =4 and x2 = 4, the objective function is 20)
a good solution was found for:
Min: 20000 - 440*x1 - 300*x2 + 20*x1^2 + 12*x2^2 + x1*x2
st: x1 +x2 =100
x1>=0, x2>=0
I understand that with heuristic solvers global optima are not guaranteed but i expected a little more robustness from a heuristics solver.
On the other hand I am not that dissapointed because a spreadsheet combined with a NL heuristic solver would be realy nice and even better if it's open source and this is one of the first.
Let's see what the future will bring.
Solver Engines and Comparators
Which solver engine did you use? As I already wrote in the Wiki, I highly recommend DEPS for most numerical problems.
Let's start with the second (the quadratic programming) problem. I just rebuilt this scenario and the solver was able to deliver the expected value in all runs. Maybe you had a typo somewhere? Also make sure that you apply the variable bounds (x1 <= 7, x2 <= 5, x1 & x2 >= 0) directly to the variable cells. Otherwise they are treated as constraints instead of bounds for the variables which makes a huge difference.
Regarding the first problem:
Don't forget that it is necessary to specify variable bounds (lower and upper limits). I specified 0 <= x1,x2 <= 10 and got a reasonable good solution (-0.85). Due to the very strict constraint (equality is a heavy burden for the solver) I then used the more restrictive ACR comparator. That lead to good results (the expected -0.75 solution) in all further runs.
So yes, the solver can't magically solve all problems on its own. But with the right tuning (at least specifying variable bounds) it can behave very well ;-)
Thanks for your feedback!
After installing OO 3.1 on
After installing OO 3.1 on ubuntu jaunty I could install the solver without any problems.
So trying again to solve my test problems with the DEPS solver and checking for typos (none made), I've noticed that for the quadratic programming problem the global optimimum solution is found but only after clicking continu after the solver stagnates, however the solution does not stick to a single value after clicking continu again. Trying different values for stagnation limit and tolerance did not help.
I'm not sure what you mean when you say to apply variable bounds directly to the variable cells? If my variable x1 is in cell $B$11 I enter it in the left hand side of the solver dialog limiting conditions section and set the correct opperator and type in the value 7.
With the first problem I've tried your tips and this helps to some extent, the optimum is found but again the solver does not stick to a single value (or close to it) but keeps moving around it by large amounts unless i provide very tight bounds (but that is of course not helpfull because then I would know the the values of the variables and thus the solution and using a solver would be pointless).
Furthermore I've tried some simple pure LP, ILP, BIP and MIP problems with 2 to 5 variables and similar number of constraints which the standard linear solver can solve with ease (most i can do by hand). The same problem is encountered again with the DEPS solver not sticking to any optimum (local or global).
In my view the DEPS and SCO solver's strenght lies in the possibility that they could solve, or give a good enough solution to ill structured or poorly understood problems. At least from a viewpoint that solving is beyond the grasp of most peoples mathematical skills when for example 10 variables are involved.
with kind regards,
John
Solver Finds Good Solutions but Settles on a Worse Solution
I'm using the 0.9 beta of the Sun non-linear solver with OOO 3.1 under Windows Vista-64 working with a 8 MB spreadsheet file.
I'm trying to maximize a value, and the solver runs and is able to improve and optimize my problem as the displayed Current Solution number in the enhanced Solver Status progress dialog states, but once either the solver computation completes the requested number of iterations, or after it hits the stagnation limits, or you hit Stop, the solver completes running and displays a much poorer (lower) result as the best one it asks whether to keep. So, for example, the optimization progress dialog might have found an optimal solution with a value of 0.49 in the progress dialog (which is very reasonable and probably correct), but the final chosen solution is a value of 0.35 once the optimization completes.
The basic problem happens with both evolutionary solvers included in the extension, with various combinations of optimizer options, optimizing both large and small numeric target values, and something similar happens for both minimizations and maximizations of the target cell. It looks like it is finding a nice solution, but displaying a fairly bad one instead of the best one in the end. The problems happen with both .ods files and .xls files.
I posted more details and a screenshot at the link below, in case anyone knows how I might workaround this or if I made some kind of obvious input error.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=19079
[In case anyone is still seeing the mentioned install error, I fixed it by uninstalling and deleting the all of the ooo settings/dirs, downloading the full ooo 3.1 installer with Java, and finally reinstalling ooo and then the latest solver beta from here].
Installation problems
Well, there's no way to install this extension on OOO 3.0 with a message:
(com.sun.star.registry.CannotRegisterImplementationException)
{ { Message = "", Context = (com.sun.star.uno.XInterface) @0 } }
Well, it's a mess because the native solver is really very poor...
Great, but...
This is brilliant, and it works really well. Kudos for the documentation with code-examples!
Unfortunately, I should say did work really well... After upgrading from Intrepid (with manually installed OpenOffice 3.0) to Jaunty (which comes pre-packaged with OpenOffice 3.0) I can't get the extension to install properly. After accepting the license agreement, a "CannotRegisterImplementationException" error is thrown.
excellent work
that is the feature I waited for for so long, works better than I expected!
It's works! Thanks to developers!
The Solver works very slow, but when I've read it's docs, I've say: "Cool! I like its idea"!
Now I unify the tables of measurement results by scale shift, multiplier and gamma-correction coefficients for every measure set. Because the tables are analog measurement results with some amount of noise and errors, it's hard to predict optimization function for the data. NLP Solver gives more precise result in comparison with Microsoft Office solver in my work. But the Microsoft solver seems to be extremely fast in comparison with this version of NLP-Solver.