Category: Uncategorized

OpenSolver for Google Sheets v2.1.0 (15 April 2016)

Today we are releasing version 2.1.0 of OpenSolver for Google Sheets! This is a minor update that brings the following changes:

  • We have added support for the GLPK solver. You can now solve problems in your browser using the open-source GLPK solver, via glpk.js by Henri Gourvest. This solver runs in the browser on your computer rather than on a cloud server, so is typically much faster than the alternatives and we heavily recommend choosing it in place of Cbc via NEOS. Please let us know if you find this useful!
  • The linearity check now occurs before solving, in order to catch non-linearities before spending time solving the model. We recently changed the linearity check in OpenSolver for Excel to be much more robust and better at detecting non-linearity, and we are pleased to roll these changes out to Google Sheets as well.
  • Bugfixes:
    • Fixed bug where dialogs could time out before they finished loading.
    • Fixed sheet-scoping issue when storing constraints in multi-sheet models.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Google’s LinearOptimizationService has been fixed as of earlier today, so the Google Linear Solver should be working again. We are happy to now offer two additional solvers in case this happens again. It took over two weeks for Google to fix this bug, which is very frustrating for all of us, and hopefully is a problem we will be able to avoid in future!

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

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!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we can”t count downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!

OpenSolver for Google Sheets v2.0 (7 April 2016)

We are very excited to announce the release of OpenSolver for Google Sheets 2.0! This is a major update that brings many improvements to the Google Sheets addon:

  • Support for one more than one model per workbook! Now, each sheet can have its own model. Use the sheet selector at the top of the sidebar to switch to the model for a sheet.
  • Models can reference cells on different sheets! You can define models using ranges on any sheet in the workbook.
  • Added a new solver: Cbc on NEOS! You can choose to either use the solver provided by Google, or the Cbc solver on the NEOS optimization cloud. Note that all problems submitted to NEOS become publicly available, please see the terms of use.
    , and use the new Cbc solver to solve models on the NEOS cloud server.
  • Support for resuming incomplete solves! If the solve takes more than 6 minutes, it will be stopped before a solution is found. Now you can resume the solve from where you left off, allowing you to solve larger models than before.

There is more information about the Google Sheets addon here on the dedicated page.

If you already use the addon, the update will automatically roll out. If you haven’t used it before, you can try it out here:

Update on Google’s Linear Solver

Last week, Google made breaking changes to their LinearOptimizationService inside Google Sheets, which means that it currently is not functional at all, and so we can’t use it in OpenSolver to solve optimisation problems. We reported the issue to Google immediately, but it has not been fixed at the time of writing this post. In the meantime, you should use the Cbc solver via the NEOS server.

If you would like to see Google fix their solver faster, you can click the star on the issue page linked before. The more stars the issue gets, the faster they will fix the problem.

Migrating models from previous versions of OpenSolver

Due to supporting one model per sheet, we have had to make changes to how we store the OpenSolver models, and so your current models are not automatically loaded into the new version. To load in your old model, you can either redefine it manually, or use the “Import previous model” item in the menu. This will load in your previous model as the model for the currently selected sheet.

Please get in touch if you have problems with this feature, we want to make the transition as smooth as possible for everyone. You should also use the “Show previous model data” menu item to open a box with the model information for your sheet. Please include this when you get in touch so that we can diagnose the problem.

Feedback and Issues

OpenSolver for Google Sheets is younger and less mature than the Excel version, so does not yet have the full range of features as in the Excel version. There are also a lot of possibilities that are created by the online aspect of Google Sheets (such as sharing sheets and multiple users on a sheet at once). This means there are many new features to consider and add to the addon as it evolves over time.

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!

We are always interested to hear how you are using OpenSolver, and in particular if you find the Google Sheets version useful (because we don’t control the downloads, we have much less user information on the number of Google Sheets users). This allows us to balance the time we spend developing the Excel and Google Sheets versions according to user demand.

If you like the Google Sheets addon, please consider rating us in the store and/or leaving us a review, which will help other users find out about OpenSolver!

Support for dynamic named ranges

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.

INFORMS schedules conference volunteers using OpenSolver

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…”

New edition of Optimization Modeling with Spreadsheets by Kenneth Baker

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

Great Maersk/OpenSolver presentation

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

OpenSolver 2.7.0 (16 June 2015)

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.