The AIMMS folks have a great online chapter describing how to take non-linear requirements (local copy of pdf) and model them using linear programming. (Thanks to Stu Mitchell of PuLP fame for pointing out this resource.)
Latest CBC Versions
If you encounter errors with problems not solving using OpenSolver, it may be because of problems in the underlying CBC solver. You can manually update CBC to the latest version by simply replacing the cbc.exe file in the folder you downloaded and extracted. Victor Zverovich from AMPL does a great job at making recent versions of CBC available for download at http://ampl.com/products/solvers/open-source/; these are compiled and ready to run.
Research & Applications
We are always interested in how OpenSolver is being used, both in research and in industry. Some of the uses we know of are given below.
Katherine Perry has submitted a masters thesis on Call Center Scheduling Problem using Spreadsheet Optimization and VBA. (You will have to click past a broken certificate.) Katherine has been using OpenSolver to solve her call center problems. Her thesis includes an appendix giving step by step instructions on installing OpenSolver that might be useful for new users.
In his Master’s thesis, “Solving a Large Scale Integer Program with Open-Source-Software,” Bernhard Aeschbacher (University of Zurich, Department of Business Administration) uses OpenSolver for scheduling. He “solved a MILP with OpenSolver with about 1’900 variables and 5’700 constraints in order to build a cyclic schedule for physicians.” It was great to see Bernard using OpenSolver’s on-sheet highlighting in his appendix to show how his model was built. His thesis and model are available to download from our Sourceforge resources repository.
Andrew Trapp, Associate Professor, Operations and Industrial Engineering, Worcester Polytechnic Institute has been using OpenSolver and SolverStudio in his student projects. Andrew’s OpenSolver projects look very interesting, and show how OpenSolver can be used in practical applications. The reports are available online, as follows:
- Improving Changeover Efficiency in Opticap® XL Encapsulation Process
- Finding Improving Solutions that Control Disruption to Binary Optimization Problems
- Improving the Scheduling of Operating Rooms at UMass Memorial Medical Center
- Optimizing Client Scheduling and Improving Client Distribution of Fundación Paraguaya
- Improving Student Placement in IQP Centers via Preference Matching
Read about OpenSolver for Route Optimization.
Other Excel Tools
This section lists other Excel add-ins that users might find useful.
Frontline Solver, who provide upgraded versions of the built-in Solver that can do very clever things because Frontline really understand how formulae work in spreadsheets.
Cplex for Microsoft Excel, which provides a powerful solver for optimization modelling in Excel. (Cplex is one of the older and better established opimisers available.) I found this description of their implementation to be very interesting; they’ve done some clever stuff!
Queueing ToolPak 4.0
The Queueing Toolpak, developed by Armann Ingolfsson and Fraser Gallop from the Alberta School of Business, provides functions to calculate statistics for “waiting line systems consisting of a single waiting line, one or more parallel servers, a Poisson arrival process, and exponentially distributed service durations (mathematically, M/M/s and M/M/s/s+C systems)”. A smaller set of statistics can also be calculate for more general G/G/s systems. The Queueing Toolpak is written in C as an XLL (and so will be fast).
Paul Jensen’s Operations Research Models and Methods Addin Collection
Paul Jensen has developed several collections of Excel add-ins that implement a very wide range of models and methods used in Operations Research, Operations Management and Industrial Engineering. These are available on Paul’s web-site which he describes as follows: The Models section contains brief discussions on how decision problems can be expressed in a form amenable to analysis, along with examples. The section includes most of the topics considered by introductory Operations Research courses. The Methods section contains pages that explain the theoretical constructs behind the solution methods, primarily mathematical programming. The Computation section provides instructions for the Excel add-ins that can be used to solve the models. A large variety of OR methods are implemented with the Excel add-ins. The Problems section has modeling or algorithm problems to be solved by the student. The OM/IE section has pages discussing the design and operation of manufacturing systems. These topics are typically taught in operations management and industrial engineering academic programs. Additional add-ins solve applied problems in OM/IE.
“Resource for Spreadsheet Analytics” – Analytics magazine Article, June 2010
This 2010 article, by Thomas Grossman, discusses many useful resources for using spreadsheets in management science and operations research.
Spreadsheet Analytics Website
This website, maintained by Thomas Grossman, is an excellent site for finding Excel tools for management science and operations research.
Accessing AlgLib from Excel using ExcelDNA
This post describes how ExcelDNA can be used to easily expose C# routines to Excel, including the numerical algorithms available in AlGLib.
RIMA (not for Excel)
RIMA is a LUA system for building linear and integer programming models developed by Geoff Leyland. RIMA’s strengths are its ability to manipulate and combine multiple models.
Ascend (not for Excel)
Survey of optimisation solvers prepared for Ascend. “ASCEND is a system for solving systems of equations, aimed at engineers and scientists. It allows you to build up complex models as as systems constructed from simpler sub-models.”
Microsoft provides this description of how Excel’s Solver works. See also the documentation on SolverOK (Office 2000) and SolverGet (Office 2000), and, with some updates, SolverGet (Office 2010). eHow provide an interesting discussion of the history of Solver. For an excellent discussion of Solver’s design by Daniel Fylstra (President and CEO of Frontline Systems), see:
Design and Use of the Microsoft Excel Solver, Daniel Fylstra, Leon Lasdon, John Watson, Allan Waren , INTERFACES, Vol. 28, No. 5, Sept-Oct 1998, pp. 29-55 (also available at http://www.utexas.edu/courses/lasdon/design3.htm)
A more recent 2003 article discusses the non-linear Solver capabilities:
Interval methods for accelerated global search in the Microsoft Excel Solver
IP Nenov, DH Fylstra – Reliable Computing, 9: 143–159, 2003 – Springer [Google Scholar] [Citeseer pdf]
A good book on using Solver is:
“VBA for Modelers: Developing Decision Support Systems With Microsoft Office Excel,” S. Christian Albright, Cengage Learning, Inc, UOH, USA (2010)
One section of particular interest is Albright’s description of Solver’s use of hidden names; see “Automating Solver and Palisade Add-Ins”, p369 of VBA for Modelers (2010).
11 thoughts on “Resources”
I am trying to get permission from my organization to add the Google Sheets plugin but the feedback I received as that the https://www.googleapis.com/auth/spreadsheets is too excessive since the script will only be executed within the context of the required spreadsheet. Is it possible to replace it with the https://www.googleapis.com/auth/drive.file scope that limits access to the specific Google Drive file used with this app?
Google has refined and change its permissions over time. We don’t currently have anyone who can make changes to the permissions in our code, but would be keen to see this change made. Perhaps your organisation or someone else can help improve OpenSolver for Google Sheets in this way? Andrew
Is it possible to set a constraint > than something else? It seems like I can only select >= in the ‘Model’ Window.
No, as > makes no sense for non-integer values like 1.0 > 0.99999999999999999999999999999999999999999999999999. Andrew
Is it possible to create schedules by interval considering requirements?
I work for a small call center and I’m looking for something that help me to optimize my current Staffing.
Yes you can use integer programming for staff scheduling. Andrew
Do you have spreadsheet sample for Single Depot Vehicle Scheduling Problem?
If yes, can you please share it with me?
Hi, is it possible to solve multiple problems with this tool?
I have successfully made a break-even calculator in Google Sheets that solves a simple variable cost (based on selling price)/fixed cost formula, but I would like to use it for multiple items –
But alas, when trying to implement over a range – “The objective cell must be a single cell. Please fix this and try again.”
You can only solve one problem at a time, and hence you can only specify a single cell as the objective.
Mike – Google Goal Programming. It’s a way to take a few different priorities into account.
Hello Andrew and OpenSolver Team:
For the interest of your audience some articles in spanish related with OpenSolver and how to use this excelent add-in can found on http://www.gestiondeoperaciones.net/tag/opensolver/.
Also basic resources about Linear Programming with with solved exercises can be found on http://www.linearprogramming.info
Hoping these can will a help for your audience.