Hi, I’ve been using your addin for google sheets and it works great.
Is there a way to set the variable cells so that it’s changing by integer values instead of linearly?
Thanks.
Hey all,
Your add-in doesnt seem to play nice with user-defined functions. I have a function, for instance, that calculates pressure drop across a bed. The solver doesn’t even try to do anything with it.
You need to choose the correct solver; each works in a different way. If you have user-defined funtions (UDF), you need to use Nomad. But, that said, a UDF hides much of the mathematics from the solver, and so is best avoided. Andrew
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 too was using OpenSolver fine on a Mac with Excel. Then I downloaded a patch for Excel (now using version 16.16.7) and OpenSolver is not working. It can let me show/hide the model, but when I ask it for Solver Option it goes into an infinite loop. Similarly, when I ask it to solve a problem, it seems to go through the setup process and then it goes into an infinite loop. It seems it is not recognizing the solvers. Just my guess.
Yes, we are aware of this problem caused by the Excel 16.16.7 upgrade on a Mac. We are looking for help from someone with a Mac that is having this issue and is familiar with VBA code. Any volunteers? 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. Thank you again for your solver. I’m enjoying learning about it. I’m a computer science undergrad from Sydney 🙂
I’m really interested in non-linear models for blending.
When I open excel and run the coin-or bonmin (non-linear solver) (or the coin-or courenne (non-linear, non-convex solver) , it works correctly the first time. Then, if i press solve again I get the following error.
I am running office 365 proplus. Excel version 1803 (build 9126.2336). Everything is 64bit.
OpenSolver is added to the list of addins.
Each time I close excel and re-open, I get one clean go at solving the same model. The solution is correct – though the model is quite trivial.
Can you make any suggestions?
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 13
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 16
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverCommon.SolveModel: Line 42
08 Jan 19 20:49:13 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 11
Error 9: Subscript out of range
Solver: Couenne
OpenSolver version 2.9.0 (2017.11.10); Distribution=Advanced
Location: “C:\Solver\OpenSolver2.9.0_AdvancedWin\OpenSolver.xlam”
Excel 16.0; build 9126; 64-bit; VBA7
Excel product code = {90160000-000F-0000-1000-0000000FF1CE}
Excel language: English – US
OS: Windows 10.0; 64-bit
Username: steve
CBC v2.9.7 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\cbc.exe”
No Gurobi installation was detected. The value of %GUROBI_HOME% was not set.
Bonmin v1.8.6 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\bonmin.exe”
Couenne v0.5.6 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (64-bit) using OpenSolverNomad v1.3.1 at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\OpenSolverNomad.dll”
Sorry it is not working. Is this because the second solve is starting from the solution found during the first run? Please try pasting back the starting solution (ie the decision cell values) from *before* the first run and see if it runs the second time. Thanks, Andrew
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.
Hi, I’m having issues using OpenSolver in Google Sheets and hope you can help!
Essentially when saving a step in the model it deletes the previous steps. As a simple example, let’s say I select cell A1 to be maximised – select the cell then press Update next to ‘Objective Cell’, and it saves that in the model. Then select the variable cells, whatever they may be, press “Add” – it does that bit right, but suddenly the Objective Cell is blank again. If I go back and repeat that step, then suddenly the variable cells are empty again etc.
I can’t see another Save button that I’m supposed to use at each step?
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?
Optimization runs for a while in excel 365, then spreadsheet closes and excel reopens looking for new file
I have not heard of that before. Is it on a Mac or on Windows? Has anyone else seen this happen? Andrew
Windows
Hi, I’ve been using your addin for google sheets and it works great.
Is there a way to set the variable cells so that it’s changing by integer values instead of linearly?
Thanks.
And a constraint requiring INTeger values for the decision variables. Andrew
Hey all,
Your add-in doesnt seem to play nice with user-defined functions. I have a function, for instance, that calculates pressure drop across a bed. The solver doesn’t even try to do anything with it.
You need to choose the correct solver; each works in a different way. If you have user-defined funtions (UDF), you need to use Nomad. But, that said, a UDF hides much of the mathematics from the solver, and so is best avoided. Andrew
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
I too was using OpenSolver fine on a Mac with Excel. Then I downloaded a patch for Excel (now using version 16.16.7) and OpenSolver is not working. It can let me show/hide the model, but when I ask it for Solver Option it goes into an infinite loop. Similarly, when I ask it to solve a problem, it seems to go through the setup process and then it goes into an infinite loop. It seems it is not recognizing the solvers. Just my guess.
Yes, we are aware of this problem caused by the Excel 16.16.7 upgrade on a Mac. We are looking for help from someone with a Mac that is having this issue and is familiar with VBA code. Any volunteers? 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. Thank you again for your solver. I’m enjoying learning about it. I’m a computer science undergrad from Sydney 🙂
I’m really interested in non-linear models for blending.
When I open excel and run the coin-or bonmin (non-linear solver) (or the coin-or courenne (non-linear, non-convex solver) , it works correctly the first time. Then, if i press solve again I get the following error.
I am running office 365 proplus. Excel version 1803 (build 9126.2336). Everything is 64bit.
OpenSolver is added to the list of addins.
Each time I close excel and re-open, I get one clean go at solving the same model. The solution is correct – though the model is quite trivial.
Can you make any suggestions?
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 13
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 16
08 Jan 19 20:49:13 [OpenSolver.xlam] SolverCommon.SolveModel: Line 42
08 Jan 19 20:49:13 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 11
Error 9: Subscript out of range
Solver: Couenne
OpenSolver version 2.9.0 (2017.11.10); Distribution=Advanced
Location: “C:\Solver\OpenSolver2.9.0_AdvancedWin\OpenSolver.xlam”
Excel 16.0; build 9126; 64-bit; VBA7
Excel product code = {90160000-000F-0000-1000-0000000FF1CE}
Excel language: English – US
OS: Windows 10.0; 64-bit
Username: steve
CBC v2.9.7 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\cbc.exe”
No Gurobi installation was detected. The value of %GUROBI_HOME% was not set.
Bonmin v1.8.6 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\bonmin.exe”
Couenne v0.5.6 (64-bit) at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (64-bit) using OpenSolverNomad v1.3.1 at “C:\Solver\OpenSolver2.9.0_AdvancedWin\Solvers\win64\OpenSolverNomad.dll”
Sorry it is not working. Is this because the second solve is starting from the solution found during the first run? Please try pasting back the starting solution (ie the decision cell values) from *before* the first run and see if it runs the second time. Thanks, Andrew
Hi Andrew, I’m getting the EXACT same problem — exact same error lines as the original poster. Did this ever get a resolution? Any advice?
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!!
Hi, I’m having issues using OpenSolver in Google Sheets and hope you can help!
Essentially when saving a step in the model it deletes the previous steps. As a simple example, let’s say I select cell A1 to be maximised – select the cell then press Update next to ‘Objective Cell’, and it saves that in the model. Then select the variable cells, whatever they may be, press “Add” – it does that bit right, but suddenly the Objective Cell is blank again. If I go back and repeat that step, then suddenly the variable cells are empty again etc.
I can’t see another Save button that I’m supposed to use at each step?
Any idea what might be going wrong?
Thanks,
Dan
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?