OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
Hello! I am wondering if there is a way to enable OpenSolver on Google Sheets for scripts/macros? I have attempted to “Record” my solver setup but when I save it the script only saved the formulas and mouse clicks from my recording, not the solver setup. Any way to solution this? I would love to automate the solver I run every day.
I suspect this would need code development, sorry Andrew
I am running a big linear programming problem size model with about 50,000 variables.
Open Solver starts setting up the problem at a constant pace of about 100 variables per second, which is quite good.
However, after setting up about 24,000 variables, Open Solver suddenly changes this pace to about one variable per second.
Any help or hint on this?
Hi Open Solver,
When I run a simple linear program to identify entries that add up to a specific value with Open Solver it does not return an answer. Then, when I run it with Excel’s solver it returns the correct answer. Is there something I’m doing wrong?
Sounds like an issue to do with numerical rounding. Try rewriting the equality constraint as twi inequalities, he <=5.01 and >=4.99. Hope this helps, Andrew
G’day Andrew and team,
My name is Steve Edwards and I am a Technical Account Manager at Gurobi. I was experimenting with the project, in particular OpenSolver2.9.3_LinearMac, and believe the `GurobiOSRun.py` is missing an argument for the status file, i.e., the following is missing from line 18:
parser.add_argument(“statusfile”, help=”Path to write the status .sol”)
Without it, the examples break due to the `param.split(‘=’)` command. Happy to add a pull request but couldn’t find the project anywhere.
Kudos on the great project!
Thanks for your post and email. Really appreciate you reaching out to us. This fix is now on our ToDo list. Andrew
Dear Open Solver Team,
I am using Open Solver dialog box to build the model manually, but this a daunting task to do repetitively when I have to build and run model 6 times by changing objective, variable and constraints.
I am able to change objective and variables using VBA, but unable to add, set or update constraints using VBA code. I tried this code :
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“Dispatch Planning Model”)
Application.Run “OpenSolver.xlam!OpenSolverAPI.ResetModel”, TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.SetObjectiveFunctionCell”, TestSheet.Range(“Z1”), TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.SetDecisionVariables”, TestSheet.Range(“y4:y22”), TestSheet
Application.Run “OpenSolver.xlam!OpenSolverAPI.AddConstraint”, 1, TestSheet.Range(“y4:y22”), RelationINT, TestSheet – this line of my code gets error.
It is more convenient to use the functions described on the manual page: https://docs.microsoft.com/en-us/office/vba/excel/concepts/functions/using-the-solver-vba-functions .
In this case, you get the following code.
SolverOK setCell:=Range(“$Z$1”), MaxMinVal:=2, byChange:=Range(“$Y$4: $Y$22”)
SolverAdd cellRef:=Range(“$Y$4: $Y$22”), relation:=4 ‘ integer variables
Ranges of cells are specified for the active sheet of the active book, but this is usually what you need.
There are examples of using functions in this reference, everything works fine.
Thanks again for your tool!
I have not found an answer to a question that seems simple to me. Server Neos uses the “Short priority: Send to higher priority queue with maximum CPU time of 5 minutes” setting in the job settings on its website. I can disable this option on the site because tasks are usually done so quickly. But OpenSolver when specifying, for example, “NEOS using CPLEX” explicitly uses “Short Priority”, and the complex task crashes after 5 minutes.
Can this be changed somewhere in the model settings?
With deep respect.
Good day, dear developers!
Thanks a lot for the great tool! I was amazed at the efficiency of solving integer programming problems for 20-30 thousand variables.
I enjoy teaching my OpenSolver students on my MBA course.
I recently came across an integer linear optimization problem that only has 161 binary variables and cannot be solved with OpenSolver (or it takes more than 12 hours to solve).
Excel file link: https://1drv.ms/u/s!ArFRI2i3V3yXl_ddltR-08_O9pumjQ?e=yfe1ze
The task schedules delivery days for 7 days of the week and 23 stores (binary variables E2: K24). I want to schedule a given number of deliveries D2: D24 for each store. The goal is to achieve a minimum difference in the loading of the central warehouse on different days of the week.
(The real problem is a little more complicated, but that’s enough.)
Surprisingly, the Calc Solver add-in (LibreOffice) solves the problem in a few minutes and gives the solution shown in the file (for example).
Can you please tell me what is the problem? The task looks very simple.
Best regards, Sergei.
I was able to figure out how to overcome the described complexity.
In my opinion, a problem occurs when a problem has a large number of equally good solutions.
Tracking the optimization process shows that the algorithm quickly achieves excellent objective function values, and then continues to look for even better options.
From the point of view of practice, it is necessary to limit the objective function to some reasonable value. For minimization, this is the lower bound.
After that, the practical problem is solved quickly enough.
Sincerely yours, Sergei.
Sergei: This is the behaviour we expect. Proving we have the best solution is often much harder than finding the best solution. Try to change your equations to remove equivalent equally-good solutions by, for example, giving staff small random preferences (i.e. slightly lower costs) for shifts (or whatever makes sense for your problem). Setting a large optimality tolerance can also help reduce run times. Andrew
First of all thanks for the excellent tool.
It would be great if you could have the possibility to give meaningful names/titles to constraints. If there is big list of constraints, it sometimes hard to find the one you would like change. Also it would make easier to understand the model being handled.
This is an idea we have on our long to do list. Thanks for reminding us. Andrew
Dear OpenSolver developers:
I am a huge fan of using Excel when modelling Process Engineering simulations.
I have been using circular references, scaling and GRG, and find it quite powerful.
However, I sometimes miss a more powerful non linear solver, such as the ones you have implemented.
I have been trying to use OpenSolver, but seems to get into trouble when dealing with circular references.
May I ask you to state if this is the case? If so, are you planning on embedding OpenSolver with native circular references?
Thank you in advance.
You should add circular references as constraints. I have not tried this, but it should work. You then need to manually break the cycle yourself; the OpenSolver constraint ‘completes’ the cycle. Let us know if it works, Andrew
Hi Andrew, definitely inspired by the opensolver. I have a nonlinear problem that causes the original excel solver to solve in around 3 mins, but thats too slow for my required procedure. I tried to use your opensolver to solve it with NOMAD and Bonmin. However, both methods try to find global solutions which cost an even longer time to solve the problem. I was trying to find a way to find a local solution, faster than 3 mins, but seems there is no local engine in your add in. I found an engine, called Ipopt, which could possibly find a local solution, but is there a way we could load new engine into your add-in please? Thank you for your time
IPOpt is inside Bonmin and Couenne, so you already have access to IPOpt. Try looking at the settings for these solvers. Andrew
Hello, OpenSolver team.
I’m always using OpenSolver in my own macros. I am very grateful to you for developing such a useful tool.
Recently, I noticed a strange phenomenon. When I run OpenSolver with a “camera tool” on one sheet displaying information from another sheet, the solving time becomes extremely long. It looks like OpenSolver and “camera tool” are interfering with each other.
If you know the cause or the solution, I would appreciate it if you could let me know.
OpenSolver does many sheet recalculates. Excel may recalculate other sheets as well, so that may be the problem. Andrew
Error in loading DLL (Error 48)，Automation error (Error 440)
Excel will report errors when running on the computer. No loss is found in the reference, but it works normally on another computer
I am just thinking about using OpenSolver, and I would like to know if it is possible to define the problem in Excel and then save it as mps or gurobi file or in another syntax. Thank you.
Yes; if you open the working files directory (using the OpenSolver menu), then you can see the .lp file. But, don’t forget that OpenSolver can run Gurobi directly for you. Cheers, Andrew
On the webpage: https://opensolver.org/opensolver-for-google-sheets/
“You can install the OpenSolver Add-on by clicking on the Chrome Web Store link below” but I couldn’t find any such link.
“When you start OpenSolver” but the webpage does not contain a description of starting OpenSolver within Google Sheets.
OpenSolver for Google Sheets is currently unavailable because of issues with changes made by Google that we are working through. Sorry about this, Andrew
I tried every free solver and minimizer available free of cost.
This is by far my favorite because it’s simple. Personally prefer this over SolverStudio because it can do what I want with minimum programming work on my side.
-minimizing one cell calculated in a spreadsheet
-larger than 200 variables. (So standard solver couldn’t handle it.)
This works. My suggestions follow
-On installation, it seems to work better with OpenSolver extracted into the MS Excel Addin’s folder. Specifically, if one clicks: file->options(at the bottom left)->Add-Ins-> Go (after manage in the middle with excel add-ins selected)-> browse (on analysis toolpak) -> it will take you to the addins folder. Copy OpenSolver to there.
-when selecting constraints and variables sometimes it seems to mess up and write the same cells multiple times. Sheet1!$A$1:$C$8 is how it should look as shown in the examples. Where ‘Sheet1!” = sheet name. $A$1:$C$8 = rows and columns from A1 to C8.
Very grateful for this OpenSolver. Well done Andrew Mason.
Your email address will not be published. Required fields are marked *