OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
Hello, I’ve been using OpenSolver for Mac, for a couple of years. It’s awesome! But, it’s now causing EXCEL to crash… endless spinning wheel after setting up the problem. Any thoughts? I note that EXCEL if fine when I erase the xlam file. I’ve even re-installed Office, but no change. I’m using version 2.9.0 with EXCEL 16.21.
Microsoft have made some breaking changes in their last Office 365 release. We are not sure what’s happened. This may be the cause of your woes. If you could help us debug the VBA, we’d welcome the support. Andrew
I’m trying to use Open Solver with COIN-OR on a spreadsheet with decision variables of 2 columns x 17k rows = ~35k variables and some simple constraints. With 3000 rows the solver part takes ~25s but the setup takes 10 minutes or so. It looks like solving with the full 17k rows is taking a lot longer.
There doesn’t appear to be any way to get output from the setup stage so I can’t check progress therein. I guess I could instrument the code myself but would prefer not to. I’d try PULP/SolverStudio with one of the commercial solvers but as this is a commercial application there’s some hoops to jump through to get an evaluation license.
Is non-linear increase in setup time expected?
And I’m running on a dual CPU PC with each CPU having 6 cores. I noticed that only one core is being used by Excel/OpenSolver during setup. Can this be parallelised? I couldn’t see any config switches.
We have to re-calculate the spreadsheet once for every decision cell (after changing that decision cell by +1) to extract the model from the spreadsheet for CBC. This can be slow, and is made worse as VBA can slow down over time for reasons (to do with memory?) we don’t understand. Excel seems to use just one core when doing these re-calculations; it is not something we can influence. You can speed this up by making sure the workbook contains only the model spreadsheet, with no graphs or other calculations. SolverStudio with PuLP and an open source solver will be much faster as no spreadsheet recalculations are required. Hope this helps. Andrew
Thanks for that. I’ll try PULP.
The Excel developers are conservative WRT using multiple cores. For example, I haven’t checked with the 365 version I’m now using but previous versions calculating Data Tables would only use one core. If you look at the complexity the Excel developers are managing in the intermediate data structures this is understandable. Ensuring there were no race conditions etc in calculations with these complex data structures would be a challenge.
Hello, my name is Steve. Today I’m testing the google sheets plugin of opensolver. I’m trying to create a blending model that will allow nonlinear property pooling in intermediate tanks (the pooling problem).
Does the google sheets version allow nonlinear blending? I can see I can include nonlinear constraints, and just uncheck the linearity check – but my testing model doesn’t appear to be obeying the constraint limits?
From the documentation, I’d guessed that non-linear blending problems such as the pooling problem for blending may be achievable. Perhaps it is with the excel version but not the google sheets version?
Thanks for your solver.
The Google Sheets OpenSolver plugin does not solve non-linear problems, sorry.
forgive me, I didn’t read enough. You’ve already answered this question. No, the google docs version can’t do nonlinear stuff.
I’m getting a error 9: subscript out of range error in windows though. I’ll post another question.
OpenSolver for Google sheets is taking quite a bit longer than usual. I am running in a workbook that I use daily and yesterday the issue began. Each time I hit solve the sidebar turns gray and takes about 3 minutes to get the “Building variables 1/3” message.
Any help would greatly be appreciated!!
Opensolver for google docs is not using constraints. I’m trying to do a simple optimization, but I can’t get it to actually use the constraints. I’m trying to use variable > 0.0001 and < 0.9999 yet it keeps setting the variable to 0. Any idea why this might be happening?
Welcome to the world of practical numerical values. Any solver has to define “effectively 0”; a value of 0.0001 can often be classed as 0. It is bad practice to specify bounds that are effectively 0 or 1, as this causes numerical instability. Furthermore, no value for a decision variable can ever get a divide by zero error in a linear model; I suspect your model is not linear, in which case we OpenSolver for Google Sheets cannot solve it. Andrew
Andrew, is fitting a two- or three-parameter Weibull distribution function to a skewed data set a linear or non-linear problem? I’m trying to figure out if the task can be done in Google Sheets, or if should be executed in Excel using the Advanced OpenSolver.
Any chances the non-liner OpenSolver will be implemented in Google Sheets?
This is non linear as you are fitting a non linear function and then taking the sum of squared errors. Non linear capabilities for Google Sheets are low on our list sorry. Andrew
Opensolver for google docs is not using constraints. Trying to solve a simple probability linear optimization (user churn), but it keeps setting the p variable to 0, when I set constraints to specifically be between 0.0001 and 0.9999. I keep getting an error ” divide by 0″ and it sets p to 0. Even if I change to add a constraint p = 10 (thus no variable), it still sets p to 0. Why might this be happening?
Your email address will not be published. Required fields are marked *