OpenSolver supports a wide variety of solvers for use inside Excel, and several different ways in which the solver integrates with the Excel model. This page gives information about the solvers, including its uses and limitations, to help you find the right solver for your problem.
Most of the solvers need to be given a copy of your model in a form they understand. To create this copy for linear models, OpenSolver uses an iterative process that requires one spreadsheet re-calculation for each decision cell. This can become slow for large models.
Versions of OpenSolver released from 2015 also include a new experimental parser that directly translates the formulae in your spreadsheet into a form the solver understands. This is needed non-linear models, and will, in the future, also be available for linear ones. We can only translate formulae that both we and the solvers understand, and so currently our parser will fail if your model uses spreadsheet-specific formulae such as OFFSET(), INDIRECT(), INDEX() etc. However, if our parser works, then the solver can typically solve your problem quickly as it doesn’t need to re-calculate the spreadsheet repeatedly.
Unlike all the other solvers, the NOMAD non-linear solver works directly with the spreadsheet. It tries out solutions by putting them into the spreadsheet and doing a spreadsheet recalculation. This process is often slower and less efficient than the other non-linear solvers, but can be used to (try to) optimise even the most complicated spreadsheets, since it can work with any model regardless of the formulae. There are some things that can slow NOMAD down, see below for more details.
Some of our solvers are linked to the NEOS Optimization Server, a cloud-based compute cluster that is free to use. OpenSolver can send your model to NEOS for solving, and then bring back the answer when NEOS is finished. Note that any model submitted to NEOS becomes publicly visible.
More detailed information on each solver is given below.
CBC (COIN-OR Branch-and-Cut) is an open-source linear and mixed-integer programming solver actively developed by COIN-OR. Questions about CBC (and bug reports) are best addressed using the CBC Mailing List. OpenSolver lets you use all of the CBC command lines options.
The Gurobi Optimizer is a state-of-the-art commercial linear and mixed-integer programming solver from Gurobi Optimization Inc. It is one of the fastest solvers available for linear and integer problems.
License: Commercial – A valid license is required to use Gurobi in OpenSolver (a free license is available for academic use). Once Gurobi is installed and activated, it will become available in OpenSolver.
NOMAD (Nonlinear Optimization by Mesh Adaptive Direct Search) is an open source non-linear blackbox optimizer that is able to solve a variety of problem types, including general non-linear problems.
Important: There are some additional considerations to be aware of when using NOMAD:
- Performance is much poorer on models with equality constraints – it is better to use inequalities
- NOMAD works best for models with fewer variables, even if there are lots of complicated constraints. It is likely to be less effective on solving models with large numbers of variables.
- If possible try to set good both lower and upper bounds on the adjustable cells in the model so that NOMAD knows where to search for solutions.
Bonmin (Basic Open-source Nonlinear Mixed INteger programming) is an experimental open-source solver developed by COIN-OR that aims to solve mixed-integer non-linear problems, where the objective and constraints are twice continuously differentiable functions.
Couenne (Convex Over and Under ENvelopes for Nonlinear Estimation) is an experimental open-source solver from COIN-OR that seeks to solve mixed-integer non-linear problems with general (non-convex) objective and constraint functions.
The IBM ILOG CPLEX Optimizer (more commonly known simply as CPLEX) is a high-performance mathematical programming solver for linear programming, mixed-integer programming and quadratic programming.
The following table summarizes the characteristics of the solvers:
|Solver||Linear||Non-Linear||Sensitivity Analysis||Uses Iteration||Uses Parsing||Uses NEOS||Advanced Only|
|CBC using NEOS||✓||✓||✓|
|Bonmin using NEOS||✓||✓||✓||✓|
|Couenne using NEOS||✓||✓||✓||✓|
|CPLEX using NEOS||✓||✓||✓|
Guide to the columns:
- Linear/Non-Linear: Linear solvers can only be used on problems where the adjustable cells appear linearly in the problem. If this is not the case, the linear solvers are very likely to return meaningless results. If a linear solver is used, there is the option to run a “Linearity Check” after the solve, which tries to make sure the problem was indeed linear. Note that this is not guaranteed to identify all non-linear problems.
- Sensitivity Analysis: Solvers that support sensitivity analysis can produce a Sensitivity Report (similar to the Excel Solver) detailing the shadow prices and reduced costs of the constraints and variables respectively.
- Uses Iteration: These solvers build the model by changing the variable cells one-by-one, building the model iteratively. This requires no knowledge of the formulae in the model, but does require that the model is linear.
- Uses Parsing: These solvers build the model by reading the formulae in the spreadsheet. They can understand a lot of Excel formulae (linear and non-linear), but do not support some functions, most notably functions like OFFSET, INDEX, MATCH etc.
- Uses NEOS: These solvers do not run on your machine, instead the model is sent to the NEOS Optimization Server which solve the model and send the results back to Excel. This can result in faster solve times for the model depending on the speed of your computer, but there can be a small delay when solving on NEOS depending on the current load on the server. Once sent to NEOS the model becomes publicly available.
- Advanced Only: These solvers are only included in the “Advanced” version of OpenSolver (which is still free and open source!). These solvers tend to be slightly more experimental than the others.
The following table contains some formulae the solvers that use parsing can understand:
|SUM||Adds all arguments together.|
|PRODUCT||Multiplies all arguments together.|
|SUMPRODUCT||Multiplies corresponding components in the given arrays, and returns the sum of those products.|
|SUMIF||Adds values in a range if the condition is met.||Not supported by NEOS solvers. Limited support when the condition relies on decision variables as we attempt to evaluate the condition once before solving.|
|MIN||Finds the minimum number from its arguments.||Not supported by Couenne.|
|MAX||Finds the maximum number from its arguments.||Not supported by Couenne.|