OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
May I know if OpenSolver provides function to download the model as lp file in Excel?
Yes; have a look under the OpenSolver menu… view last model file.
Thank you again for this incredible tool.
Is there any option how to set up constrains not by using special window (model constuction)- but for example by using cells?
It would be better way to set up lot of similar condition. I tried find out via vba, but it doesnt help.
No, you cannot set up constraints directly with formulae that you can copy and paste. But if you keep the model layout clear then you can use AutoModel. Also, large groups of identical constraints can be entered just once. Hope this helps, Andrew
Thank you for the answer.
Unfortunatelly Automodel cant help the model is difficult.
Also I am limmited with the error (Error: the cell range specified for the Variable Cells is invalid. This must be a valid Excel range that does not exceed Excel’s internal character count limits. Please correct this and try again.) which appers after adding other variable. Is there any limits for count of variables? Or does it occur because my variable are used by named ranges with lot of charaters?
Thanks in advance!
Long references won’t work because Excel rejects them. Group your variables togther on the sheet. Andrew
And what about by using macro? After look in OpenSolver API Reference I would expect that is possible use macro like:
Opensolver.AddConstraint(Range(“XXX”), RelationINT, , , Sheets(“Sheet_A”))
But it doesnt work.
Thankss very much for your help.
OH I found!
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“test”)
OpenSolver.AddConstraint TestSheet.Range(Cells(3, 1), Cells(3, 4)), RelationBIN, Sheet:=TestSheet
Hi @ All!
First – thanks a lot for a wonderful toll, which I use for years to teach Students, how to use optimization to solve real-life problems!
But since few weeks – I and my new course got a huge problem – new participants have no chance to connect Google Sheets with Open Solver – because everybody gets “Sign in with Google temporarily disabled for this app. This app has not been verified yet by Google in order to use Google Sign In.”.
How can we fix it?
I have a model that have 9 constraints, but for the information there is no feasible solution so it load a solution that does not comply with all 9 constraints, there is a way to set some constrants as optional so when it solve check that the non optional constraints comply and the optional may or not comply the restriction set to it.
thanks in advance.
You can change the constraints to add in slack/surplus variables, and then put costs on these new variables so their value is minimised. See, eg, https://sites.google.com/site/decisionmodeling/Home/mp/lp/ssv Hope this helps, Andrew
Great work. I really enjoy working with OpenSolver. With Gurobi solver it is indeed a powerful tool. Thank you for making it open to the research community.
I was wondering… sometimes setting up the model takes a lot of time (regardless of the computer parameters). In the model, there is a set of parameters that are not the “right side” of the constraints. These parameters are subject to change after each run of the model (sort of Monte Carlo analysis). Is there a way to “tell” the software that I will be changing these values between individual “solves”?
Thank you for your help on this matter!
If you look at the Quick Solve feature, it can do this, but only for the right hand sides. Otherwise, you have to do a full solve, sorry. Andrew
Any idea by when the transfer time between Google Sheets and OpenSolver will be drastically shortened?
OpenSolver works fine in Excel. Replicated the model in Google Sheets. Solves fine. However, transferring the problem – approx. 800 variables, and 40 restrictions – prior to solving (1 second) takes like 5-10 minutes. This would become several hours, even days, if I would scale up to a real sized problem. Thus making it unworkable.
You can simplify the spreadsheet to make the model extraction faster (by simplifying the spreadsheet recalculations), or try a solver that is ‘parsed’ (see the solver web page), or change to SolverStudio. Andrew
I have stumbled upon a strange behavior with a non-linear model (the Excel file had been already setup by someone else). The COIN-OR CBC didn’t complain that the problem was non-linear and found a solution, clearly sub-optimal.
The objective function is a sum of product of continuous and binary variables. Is it possible that OpenSolver has been tricked by a particular setup?
We do only quick checks for non-linearity; these checks can miss cases like yours. The user has to ensure the model is linear. Cheers, Andrew
I’ve been solving a linear problem with no issues for a while now. Added another constraint and am wondering if it’s either not linear, or something is wrong.
I’m getting an error: “OpenSolver 2.7.1 encountered an error:
BuildConstraintMatrix: Line 1305
BuildModelFromSolverData: Line 1186
RunOpenSolver: Line 0
using CBC 2.7.1
Any thoughts when OpenSover will be comparable with MAC OSX Mojave?
I am using “Microsoft Excel for Office 365 MSO (16.0.11727.20222) 32 bit”. I downloaded “OpenSolver2.9.0_Linearwin.zip” and extracted the files to a directory. I doubled clicked on “OpenSolver.xlam” and Excel opened, but no OpenSolver commands appeared under the ‘Data” tab of the toolbar. Using “file/options/add-ins” there was no indication that OpenSolver had actually loaded.
Any assistance will be greatly appreciated!
Okay, I solved this using the instructions here:
Specifically: “Some Excel add-ins are located on your computer and can be installed or activated by clicking Browse (in the Add-Ins dialog box) to locate the add-in, and then clicking OK.”
Pleased that it worked ok. Thanks for the useful install link. Andrew
hello i want to deactivate the opensolver msg box when the solution is not optimal.
I sincerely appreciate your generosity in making this program freely available. I wanted to alert you to a bug involving named ranges. I added an OpenSolver worksheet to a financial spreadsheet that tracks my stocks and assets and has a few dozen named ranges. When OpenSolver runs, it replaces a lot of named ranges in pre-existing formulas (not OpenSolver related) in the workbook with OpenSolver-related named ranges. Perhaps if I protected those pre-existing formulas this wouldn’t happen.
Cool project. I’m using it with an Excel model written in VBA which runs much slower in Excel 2016. This lets me use the NOMAD non-linear solver with the faster Excel 2007 VBA.
Andrew may you please create a solver plugin for onlyoffice?
How does OpenSolver evaluate derivatives for non-linear problems? Does it use finite-differencing or algorithmic differentiation, like, e.g., CasADi does?
If we parse the formulae then they are differentiated by the solver, eg by using chain rule I think. The Nomad solver does not use derivatives; it uses a grid style search. Andrew
Your email address will not be published. Required fields are marked *