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: Solver_problem.xls