OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
I am using a Logical OR in my objective function. My decision variables are binary. Hence it is a nonlinear model. Which nonlinear model is most suitable for me?
Binary variables do not make a model non-linear. Assuming the model is linear in the variables (binary and others), then use CBC. Andrew
Hi, congratulations to the tool.
Is it possible to define semi-continuous constraints in Open Solver? How can I do it?
So, you want either x=0, or say 10<=x<=20. Add a binary variable z, and add constraints x<=10z, x>=20z. Good luck. Andrew
Hello OpenSolver Team,
just wanted to let you know that the current download link on your home page (“The latest stable version, OpenSolver 2.8.6 (6 Mar 2017)”) actually initiates the download of version 2.8.5. (Of course, Version 2.8.6 is directly available at sourceforge.net)
Oops! Thanks for letting us know; we’ve fixed that now. Andrew
Great product guys! Greetings from a fellow modeller in NZ.
I’m running OpenSolver through VBA in Excel. I want to output the dual prices/sensitivity analysis to a specific range in my worksheet. I presume I’m meant to use the GetDuals function but I can’t figure out the syntax.
Can you refer me to some example VBA code that uses GetDuals to output the dual prices?
You want to use `SetDuals` before running the solve. This sets the output range, and then when you solve the model it will be populated with the results.
Thanks for developing this great tool!
I’ve noticed that whenever I set Excel 2016 to automatically load the Open Solver add-in, it changes the default option of formula calculation (found under File>Options>Formulas>Calculation Options) to manual, which proves to be quite a nuisance. Disabling the add-in solves the problem, but upon re-enabling it, the problem resurfaces, effectively leading to me needing to choose between having turned on by default either automatic calculation, or Open Solver. Are there any workarounds for it?
This is fixed in the 2.8.6 release which is coming any day now
new to this,
trying add nomad to my excel i cant seem to find dll file. Please help
First of all, thank you for this wonderful tool. I’ve made a wonderful tool with it, that I would like to make available to users on an Ipad. I was wondering if there is any work being done, or progress to report on making opensolver work with the free version excel for Ipad?
Thanks and best regards
Thanks for the good feedback. No plans to support Excel on the IPad, but we’d welcome someone joining us to add such support to OpenSolver in the future. Andrew
Im getting this error when using Bonmir
OpenSolver 2.8.5 encountered an error:
Out of stack space
An error log with more details has been saved, which you can see by clicking ‘More Details’. If you continue to have trouble, please use the ‘Report Issue’ button or visit the OpenSolver website for assistance:
any hints to solve this?
It is probably a very large summation, eg sum(A1:A9999). You could split this into several smaller sum’s. Other than that, the only option is to wait for us to re-write the formula parser, which is on the radar but is a big job. Sorry not to have a better response at this stage. Andrew
Many thanks to Andrew for this point.
It works really!
I split a SUMPRODUCT of two large matrix into the SUM of the results of several smaller SUMPRODUCT of the colums of the matrix.
It works well.
First, thank you. OpenSolver / SolverStudio is fantastic.
Second, a question. When I solve a model that I translated to Solver Studio I get different results if I solve repeatedly. Some results do not adhere to all the constraints, but one solution of the several does and is the optimal solution (i.e. it matches the results from OpenSolver, and appears to be correct). Any idea why this happens? I am using some one if statement along with the sumproduct statements. Could that be causing it?
It could be the if(), a mistake in your model, or multiple optimal solutions (within the tolerance you specify). Andrew
Hello. I´m working in a big company and we are starting to use google spreadsheets. I want to know if the addon in google is free to use in companies or there is a fee in this case.
Thanks for your time.
You are most welcome to use OpenSolver for Google Sheets; there is no restriction or cost for company usage. We hope you find OpenSolver useful. Andrew
Hello, im currently working at an optimisation solution, but i’ve encountered some problems, when there isn’t a feasible solution, the solver breaks some constraints. For me to be usefull it needs to break one of them specifically, in older versions of my excel it worked like i would like to, but in this new version im working with, it doesn’t (apparently nothing has changed relative to the model of the solver), can I manipulate the solver in any way to obtain the results just like I want them to have when there isn’t a feasible solution?
If your model is infeasible, then you can’t have any expectation on what solution it will terminate with (if any). For your use case, it sounds like you could remove the objective and replace it with the amount of violation for the constraint you are willing to break. Minimizing this will give the “most feasible” solution, subject to only being able to break the constraint you have identified
But in previous versions of my Excel file, I could get the results that I want to get, being the model still infeasible, I think I made it by swapping the order of some constraints, and adding redundant ones, but im not being able to reproduce the same type of results. Its like i would like to choose which constraints the solver should break when finds an infeasible model.
That worked before because of good luck. As Jack said, you need to add slack variables (one per constraint) to create “elastic constraints”, and then minimise the (weighted) sum of these. See, eg, http://lpsolve.sourceforge.net/5.5/Infeasible.htm. Good luck. Andrew
Hi, I have just started using the Googlesheet Addon. Just wondering if it is possible to set up the data and model in Googlesheet and run the model from other environment, such as R? Thank you
I don’t think there is a simple way to do this, but I think it is technically possible if you deployed your Google Sheet as a webapp and included the OpenSolver code in the webapp, which would then be able to communicate with R. This page has the information about the webapp part of things, but I can’t be of any more help beyond this as I don’t have any experience with this sorry.
Thank you for making Open Solver available for public use! We used it to optimize our clinical schedules at Central Michigan College of Medicine. We’re presenting our new method at the AAMC conference on November 11th. We’ll be sure to site your website and paper. Hopefully, we can generate more interest in using optimization for a variety of problems.
I am pleased it has been useful. Most of the credit should go to Coin-OR for developing the solvers. We just wrap them up to make them easy to use. Andrew
Thanks for sharing OpenSolver, first.
I am using Excel 2016 in Windows. And OpenSolver has suddenly started not working. I tried to upgrade it to the latest version but no way. It opens the model, but I got no reaction when i want to click on constraints. What might be the problem? What should I do? If it is a problem from your side, it would be super great if it is resolved as soon as possible.
This problem has been triggered by a bug in the October update to Excel 2016 from Microsoft. We will be releasing an update soon that avoids this bug, but in the meantime you can access the constraint information by using the Tab key to move around the Model form.
When running automodel in version 2.8.4 I get the error message that an issue arose while finding constraints, error nr 91. “Object variable or with block variable not set at line 12”. I could probably repair it myself if I knew in which module.. which wouldn’t help other users.
Besides that, when I’m closing Excel there’s another error message abt run-time error 1004 Programmatic access to VBP is not trusted. I traced the error to class module “CodeExporter”, Public Sub “Export to” which you probably already would have guessed.
Btw I’m working in Excel 2007.
Your email address will not be published. Required fields are marked *