Created attachment 126212 [details] File with optimization problem Hi all! I have calculated a simple optimization problem: A cylinder should have a volume of 1000cm³. How big must be height and radius to ensure that the surface is minimal? In Excel we get after two seconds the (right) solution with radius = 5,42cm and height = 10,84cm. But Calc gives me (after more than 15 seconds): Radius = 3,65cm, height = 23,90cm (see attachment). The calculation was made with DEPS Evolutionary Algorithm. What's the problem? Thanks for your help and best regards Matthias
So what should I do with the file? Lay it down like I'm some trained monkey. Go to Tools - Solver, input this into field x etc.. Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the information.
Created attachment 126277 [details] Solver data Hi, sorry - you're right. I assumed that the solver data remain stored in the file. Here the necessary information. Thanks for your help. Best regards Matthias
Confirmed. Still, there is not guarantee that a evolutionary algorithm will find the optimal solution to the problem so I would say the result is something that could be expected. If you tune the parameters and add additional constraints you will arrive at a better solution - I could even match the one from excel. No wonder excel writes a better solution as they bought their solver from the experts ;) I'm working on a simple evolutionary solver so we can have a NLP solver that can be included in LO without the need to have Java. I'll try to tune it so that it can find simple problems better but I'm not an expert and the solver is still a WIP.
Hi Tomaz, that should not be a fundamental critique. The solver is good in principle. Thanks for that! I just wanted to know what settings I need to change, to obtain (if appropriate) a better or the best result. Maybe comes there still some mathematical geniuses together and create an even better tool ... ;-) Thanks and best regards Matthias
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.4.1 or 5.3.6 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170901
The bug remains - also in version 5.4.1.2 :-/ Calc does not find any solution at all, which is even close to the right result. Either it stops immediately because of reaching a stagnation or iteration limit or the results are completely absurd. The test was performed with the standard settings of the DEPS Evolutionary Algorithm. What can I do?
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Created attachment 151135 [details] Print screen of test data setup. Two more constraints.
I would like to suggest two more constraints in the example. Radius and height should be positive numbers.
DEPS solver is hybrid heuristic optimizer. It is based on Differential Evolution and Particle Swarm Optimization. If you look a little bit more of DE's chart-flow diagram you will see that DE works well with high dimensional spaces: http://www1.icsi.berkeley.edu/~storn/de2.jpg The example given in this bug report has only two variables (radius and height), which means that this is two-dimensional space. The constraint of 1000 volume makes the situation even worse. We call this kind of constraints "hard constraints", because usually they are difficult to be satisfied. DE is in the group of the genetic algorithms. It has crossover operation: https://www.obitko.com/tutorials/genetic-algorithms/crossover-mutation.php When you have only two variables your chromosomes have only two components. Literally you do not have what to crossover. It is absolutely expected that DE will perform bad in such situations. I am not good in PSO part of the solver, but PSO is an optimization heuristics in the rank of DE. Even if you check the source code you will see that PSO has pretty similar implementation to DE. This means that PSO also is not proper for low-dimensional problems. I am not using Microsoft Excel (generally I am not using Windows) and it is difficult for me to test this example in Excel, but there is no direct correspondence between Excel solvers and Calc solvers. It does not matter that Excel solves this example efficiently. First of all most of the source code in Microsoft's products is closed. May be they are using some kind of DE, GA, PSO or something else, but may be they are using also something extra, which is particularly implemented for low-dimensional cases.
Excel's Sover uses a deterministic method to estimate a solution and Calc's does not (DEPS uses heuristics). Well, how I circunvented the problem? My limit conditions were $B$9 >= 999.9999 and $B$9 <= 1000.0001. For initial guess I forced radius value 5 and height was guessed with a GoalSeek to V = 1000. Why that? Because even an excelent "root finder" depends on the initial guesses. And the same solution was found with a bit more higher number of interations when compared to Excel.
Created attachment 164396 [details] SolverErrorSuccess.ods Personally, I always try to reduce constrains by computing the mean square root. Please find enclosed the example that always success. I removed the constrain "volume must be 1000" by computing the following value : (1000-volume calculated)^2+surface. The goal is to have the minimum (subtract must be zero and surface must be the lowest possible). But it's true it does not solve the initial problem.
Created attachment 164397 [details] SuccessSolverOption.png
Dear MatthiasRw, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
LibreOffice still can't find the right solution. Version: 7.5.0.0.alpha0+ (x64) / LibreOffice Community Build ID: e93b7f6a5c5f9ee86546d95d7fe70ecc26b71b91 CPU threads: 16; OS: Windows 10.0 Build 22000; UI render: Skia/Vulkan; VCL: win Locale: fr-FR (fr_FR); UI: fr-FR Calc: CL I tried to use ceres-solver library to find the solution. Library didn't find the right solution. I think it's not an easy problem.