Category: Uncategorized

One of the features that we have enjoyed in SolverStudio is support for dynamic ranges, i.e. named ranges that are created using formulae. OpenSolver 2.8 and later now supports this, and so you can use formulae to define ‘dynamic’ named ranges that are then used for the left hand side and right hand side of constraints, for the decision cells and for the objective. So for example, instead of having a constraint left hand side of A1:A4, you can defined a named range Constraint1LHS with a formula such as “=offset($A$1, 0, 0, COUNTA($A:$A),1)”. This range is dynamic in that it automatically expands as more values are entered into the A column. You can then set up a constraint in which you enter Constraint1LHS for the left hand side range. This means your model automatically works as the user changes the data on the sheet. For more information on dynamic range, see excel-easy.com or ozgrid.com. We hope you find this useful.

You may wish to view this new YouTube OpenSolver introduction by Kevin Jia (Engineering Science department, University of Auckland)

We are going to test out CloudFlare for the OpenSolver web-site; DNS propogation is happening now to switch us over. Let’s hope it goes smoothly. Andrew

http://meetings2.informs.org/wordpress/philadelphia/2015/11/04/optimal-student-volunteer-scheduling-for-the-2015-informs-annual-meeting/

“When we sat down to begin scheduling student volunteers for this year’s Annual Meeting, we recognized an opportunity to use OR methods to make the task more manageable. We realized that the problem consisted of scheduling 59 students across eight shifts (two shifts per day, four days), with each student required to work either one or two five-hour shifts (morning or afternoon). Since student volunteers are placed throughout the conference facilities, we decided to schedule students into the 74 “shift-location” combinations rather than simply assign a block of students into each shift.

A Doodle poll was set up to obtain student availability. Each student was asked to indicate five out of the eight shifts during which they were available. The availability data was translated into a cost matrix where the cost was zero if a student was available and a cost of 1,000,000 if a student was unavailable for a certain shift. The objective was to minimize the total cost of the assigned schedule, thereby creating a feasible schedule that did not assign any students to shift(s) for which they were unavailable.

The problem was set up and solved in MS Excel. The tool used was COIN-OR’s Open Solver add-in for MS Excel…”

Want to be part of our OR/Analytics team at the Department of Engineering Science, University of Auckland in NZ? Check out this job posting.

Just came across a great blog on building the best Fantasy Football team using Excel/Solver/OpenSolver by Tallys Yunes.

Read about OpenSolver being used to reduce maintenance costs in “Safety and Reliability of Complex Engineered Systems: ESREL”, 2015, edited by Luca Podofillini, Bruno Sudret, Bozidar Stojadinovic, Enrico Zio, Wolfgang Kröger.

Kenneth Baker is well known for his excellent introductory texts on optimisation modelling using spreadsheets. It is great to see that his latest edition of “Optimization Modeling with Spreadsheets” (ISBN: 978-1-118-93769-3, July 2015) now includes an online appendix on OpenSolver. I look forward to seeing a copy of this new edition when it arrives. Andrew

I am on sabbatical at DTU in Denmark, and just enjoyed meeting Aleksandr from Maersk in Copenhagen who is doing some great work with OpenSolver (and perhaps SolverStudio in the future) to help make better decisions at one of the world’s largest shipping companies. Aleksandr presented some very sophisticated OpenSolver models, and is one of the few users I know of who uses the Quick Solve feature. (Any more of you out there?) Thanks, Aleksandr, for a great talk, and giving us some challenging models to think about as we plan for faster solve times in future releases. Andrew

Today we are happy to announce the release of OpenSolver 2.7.0, available for download here. The release fixes a large number of bugs and also introduces some great new features:

• Update Checker: OpenSolver now includes an automatic update checker that will periodically check for updates. This is an opt-in feature that we hope will make it easier to keep up with the latest releases. As a side effect, we plan to move to more frequent releases so that new features and fixes are available sooner. More info on the Update Checker is available here.
• VBA API: We have created a complete VBA interface to OpenSolver that allows full control of OpenSolver using VBA, much like Excel Solver’s API. We strongly recommend using these new functions to control OpenSolver instead of the Solver functions. The full list of API methods is here.
• Error Reporting: We have revamped the error handling within OpenSolver to give us much more information when an error occurs. There is also an option to report issues from within Excel when an error occurs. This will email us directly with full information about the error, which will hopefully make it easier to resolve any issues you might encounter.
• All solvers now support extra parameters being defined, and these parameters can now be set from the Options dialog. More information about using these parameters is here.
• Highly experimental support has been added for the NOMAD solver on Mac. This feature is very new, so if you try this out please let us know how it goes.

We have also updated the included solvers to the following versions:

• CBC 2.9.4
• Bonmin 1.8.1
• Couenne 0.5.3
• NOMAD 3.7.2

As always, please let us know how you find the new version, and we appreciate any feedback you can offer.

There’s an interview online at Chandoo.org with Dan Fylstra, the creator of Solver, the add-in which has made such a contribution in bringing optimization to the millions of Excel users.

Building good linear and integer programming models is not easy. However, I have just come across the AIMMS Optimization Modelling book which covers many important modelling ideas. The AMPL book is also a good reference that I can fully recommend. Enjoy. Andrew

We had a report yesterday from a user Daryl that BitDefender was reporting a possible virus infection on his PC that appeared to be connected with OpenSolver. Thanks to great work by Daryl and our programmer Jack, we identified this as a false positive (i.e. an incorrect report of a virus) being triggered by BitDefender’s ‘heuristics’. BitDefender was objecting to some of our VBA code, including one of our comments. (Note that I said comment, not actual code, and so this is definitely a case of BitDefender being overzealous!) We will tweak our next release to better avoid false reports such as these.

Thanks, again, to Daryl for contacting us with his concerns. Much appreciated.

Andrew

This user feedback helps summarise why we created SolverStudio, our free tool for Excel users that want to build bigger optimisation models:

Dear Andrew,
Couple of months ago you had advised me to solve [my OpenSolver] model (which takes 1.5 hours to even create set up file) in SolverStudio.

I followed your advise and now I would like to share my real life experience about such a big model.
While OpenSolver was solving my large problem in 2-2.5 hours (including setup and solution), SolverStudio GAMS formulation reduced the solution time to 2 minutes. I’d like to take this opportunity to thank everyone who developed such a tool. Also, SolverStudio greatly quickened up NEOS solver use. It was kind of painful to NEOS solver on its own…

We are excited to announce that OpenSolver has made it to Google Sheets, letting you solve your optimisation problems with OpenSolver from anywhere!

With the OpenSolver Add-on you can solve both linear and mixed-integer programs from within Google Sheets with no additional setup required. We use the excellent, open source solvers Glop (for LP) and SCIP (for MIP) to solve the problems.

You can install the OpenSolver Add-on by clicking on the Chrome Web Store link below and then clicking on the “Free” button. You can also find us by searching for “OpenSolver” in the Google Sheets Add-on store. Once the Add-on is installed, you can find OpenSolver in the “Add-ons” menu inside the spreadsheet.

This is our first release of this Add-on, so please let us know how you find it! You can either leave messages here, or leave feedback on the Chrome Web Store by following the link above. If you use the Add-on and like it, please consider leaving us a review in the store so that we can move up the rankings inside the store.

There are some currently some limitations, mainly that the model can only be contained on a single sheet. This is something we intend to fix very soon.

If you have any feature requests or experience any problems while using it, please let us know either here or by using the “Report an Issue” button inside Google Sheets (you can find this in “Add-ons > OpenSolver > Help”). If you use “Report an Issue”, please make sure that you include your contact details, otherwise there is no way for us to follow up!

Give it a try and let us know how you find it!