I tried to download the add-in but it did not show up in the Data-tab of my Excel. As I do not need the program anymore I tried to delete it – but now Excel sends an error message every time I open it (“OpenSolver file seems to be moved or deleted”). How should I uninstall/disable the program? It is slowing down my Excel and entire computer significantly. Already tried to uninstall Office but it did not solve the problem.
Sorry OpenSolver did not show up for you. It sounds as though you installed it to load automatically each time Excel starts up. (There is no need to do this; I just open the OpenSolver.xlam file when I need it.) Like any add-in, you can stop OpenSolver being loaded from within Excel; see this Microsoft documentation. By the way, OpenSolver cannot slow down your computer. Furthermore, it is designed to have minimal impact on Excel in that it deliberately does not run any code unless the user interacts with it, and its start-up code is kept to a bare minimum. Hope this helps, Andrew
Hello – I’m trying to use OpenSolver with Google sheets. I am trying to use a constraint wherein D15:D30 (i.e, 16 cells in the range) should be equal to ‘0’ or ‘1’. How do I define this constraint? Please help.
Thanks very much for OpenSolver which has loaded fine into Excel 2016 under Windows 10. My application is in a highly non-linear and non-smooth workbook; hence, the ‘Nomad’ method. It’s giving me more usable results than does Excel’s Solver ‘Evolutionary’ method.
However, I notice that several parameters available in Evolutionary (specifically Mutation Rate and Population Size) don’t show up in the Nomad options. Is it because Nomad sweeps widely over these anyway, or some other reason makes them moot?
I can let my PC go for many hours on a run, and basically want to encourage the solver to poke around thoroughly before quitting. So I like having method variables open to me – but that doesn’t mean that I know anything about what they really accomplish vs letting the engine do it’s natural best.
Thanks for the nice comments; good to hear Nomad is working well for you. Nomad uses a pattern search, and so has no ‘population’; evolutionary algorithms work quite differently (using ideas from evolution which often have a somewhat tenuous link with optimisation 🙂 ). You can find the Nomad documentation online. OpenSolver can pass solver parameters through to a solver if you set them up on the sheet; this can give you fine grained control of the solver. For more info, please see “Extra Solver Parameters” on https://opensolver.org/using-opensolver/
Hope this helps, Andrew
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
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?
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.
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?
Dear team at OpenSolver,
I tried to download the add-in but it did not show up in the Data-tab of my Excel. As I do not need the program anymore I tried to delete it – but now Excel sends an error message every time I open it (“OpenSolver file seems to be moved or deleted”). How should I uninstall/disable the program? It is slowing down my Excel and entire computer significantly. Already tried to uninstall Office but it did not solve the problem.
Many thanks!
Sorry OpenSolver did not show up for you. It sounds as though you installed it to load automatically each time Excel starts up. (There is no need to do this; I just open the OpenSolver.xlam file when I need it.) Like any add-in, you can stop OpenSolver being loaded from within Excel; see this Microsoft documentation. By the way, OpenSolver cannot slow down your computer. Furthermore, it is designed to have minimal impact on Excel in that it deliberately does not run any code unless the user interacts with it, and its start-up code is kept to a bare minimum. Hope this helps, Andrew
Hello – I’m trying to use OpenSolver with Google sheets. I am trying to use a constraint wherein D15:D30 (i.e, 16 cells in the range) should be equal to ‘0’ or ‘1’. How do I define this constraint? Please help.
Thanks very much for OpenSolver which has loaded fine into Excel 2016 under Windows 10. My application is in a highly non-linear and non-smooth workbook; hence, the ‘Nomad’ method. It’s giving me more usable results than does Excel’s Solver ‘Evolutionary’ method.
However, I notice that several parameters available in Evolutionary (specifically Mutation Rate and Population Size) don’t show up in the Nomad options. Is it because Nomad sweeps widely over these anyway, or some other reason makes them moot?
I can let my PC go for many hours on a run, and basically want to encourage the solver to poke around thoroughly before quitting. So I like having method variables open to me – but that doesn’t mean that I know anything about what they really accomplish vs letting the engine do it’s natural best.
Thanks
Thanks for the nice comments; good to hear Nomad is working well for you. Nomad uses a pattern search, and so has no ‘population’; evolutionary algorithms work quite differently (using ideas from evolution which often have a somewhat tenuous link with optimisation 🙂 ). You can find the Nomad documentation online. OpenSolver can pass solver parameters through to a solver if you set them up on the sheet; this can give you fine grained control of the solver. For more info, please see “Extra Solver Parameters” on https://opensolver.org/using-opensolver/
Hope this helps, Andrew
Hi,
What’s the right way to enter a constraint in OpenSolver where you want cell a1 <= b1, a2 <= b2 … a1000 <= b1000?
I had a1:a1000 <= b1:b1000 which was running but the lp file shows a lot of bounds info being created.
And if I increase the row count beyond several thousand problem setup time blows out.
Is there another way to set constraints of this nature?
Thanks
David
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.
Thanks,
Greg
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
Hi,
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.
Thanks
David
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.
Andrew
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?
–Tony
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?