Why does LibreOffice Solver not get an answer when Excel does

asked 2017-02-11 19:03:03 +0200

carl gravatar image

updated 2017-02-14 16:39:13 +0200

I have a reasonably simple spreadsheet needing Solver to minimise a target cell. There is one movable cell. Now, I know that if the movable cell could be set to negative, my target cell would go to zero, so I set a constraint that the movable cell must be greater than or equal to zero.

To test, I plugged in some values:

moving cell 1,000,000. Answer cell 205,600

moving cell 1,200,000. Answer cell 206,538 (and i can confirm that going higher and higher will continue to increase the answer)

moving cell 800,000. Answer cell 206,846 (again, I know for a fact that decreasing the moving cell will increase the answer cell).

So there is an optimum somewhere between 1.2m and 800K.

LibreOffice tells me the problem is unbounded. If I take out the limiting condition (moving cell must be greater than zero, then it comes up with a solution but it is totally wrong - it sets the moving cell to some massive number so the answer cell is also huge). Interestingly, if I tell it rather the MAXIMISE without the limiting condtion, it comes up with a negative solution so that the answer cell goes to zero. If I insert the limiting condition then it comes up with a solution of zero, which produces a result but certainly not a maximum (as searching for a maximum, is, in fact an unbound problem).

So, for one thing, LibreOffice seems to get maximise and minimise backwards, attempting to lower the target value as much as possible when told to maximise and vice versa.

And secondly, it doesn't get the answer right even if it tries. If it was merely a case of telling it to maximise when you want it to minimise, then you could still get the right answer.

As it happens, I used the exact same spreadsheet and used Excel's solver with exactly the same constraints and it got the right answer without a hitch.

Any ideas? Known bug? I'm happy to share my sheet if it would be useful for analysis.

Managed to work out how to upload: C:\fakepath\Solver_problem.xls

edit retag flag offensive close merge delete


I have no idea, but have given you some karma so you can share the sheet. Maybie someone can help

Zeca gravatar imageZeca ( 2017-02-12 14:26:47 +0200 )edit

Okay thanks, I shall upload the file as soon as I work out how to. The yellow cell is the moveable cell and the red cell is the target which should be minimised.

carl gravatar imagecarl ( 2017-02-13 11:27:29 +0200 )edit

I tried to see how to upload. Apparently there's meant to be a paperclip somewhere. I can't see it. Any assistance appreciated.

carl gravatar imagecarl ( 2017-02-13 11:36:09 +0200 )edit