I learnt a lot working on OpenSolver 1.1beta. For example, did you know that Excel’s union operator can produce ranges with duplicate cells!? Pearson describes the problem here. (We haven’t implemented this yet in OpenSolver as it may slow things down, but will do if it causes problems.) I also realised that Excel allows ranges (such as for the decision variables) like “A1:A5,A2:A6” that internally overlap; OpenSolver now internally fixes ranges like this when they occur.
I’ve always told my students that Solver’s “Assume Non-Negative” applies zero lower bounds to all the variables. I was wrong, as demonstrated by Solver’s new wording “Make Unconstrained Variables Non-Negative”. Lower bounds are only applied to variables that don’t have ‘explicit’ lower bounds set for them in the constraints. ‘Explicit’ here means the variable appears in the left hand side of a constraint which has a right hand side specified by a range or a constant. However, my preliminary tests suggest that Excel 2010 also requires that the left hand side of the constraint not include any non-decision variables. This can lead to different solutions when you move to Excel 2010, which seems rather strange. OpenSolver used to apply zero lower bounds to all the variables. The new version now implements the 2007 approach; you can see this in the new Bounds section in the .lp files.
Finally, did you know the right hand side of a constraint can contain a formula? This is a direct consequence of Excel allowing formulae to be entered as a “named range” (not that it is actually a range!). OpenSolver now handles these (not that I’d suggest you use this obscure feature).
No, my only question and comment were about the named ranges. I didn’t have any issue with the non-negatives. I guess my comment was titled just like the blog post above is titled.
Thank you
Mario
Mario, the new version of OpenSolver (which is almost ready to release) gives the user the option of showing named ranges. I’m not sure how well it will work with OFFSET and other functions; please give us your feedback once we release the new code. Your post title had ‘Assume Non-Negative’ quirks; is there an issue we need to know about? This non-negative option should impose zero lower bounds on all variables that do not have any other lower bound specified for them. So, if you specify a negative lower bound on some variable, this will override the “assume non negative”. Hope this helps, Andrew
I’ve noticed that, after I define named ranges, Solver picks them up and displays them correctly in the model menu but OpenSolver doesn’t (it keeps displaying the cell ranges). Is that so? Or am I doing something wrong?
Regarding the formulas in the named ranges, I’ve found very helpful to use OFFSET to have a dynamic range, seems to work for both Solver and OpenSolver.
Regards,
Mario