Hi, I have a sheet with quite a few constraints. A previous version of OpenSolver could run through this problem but not giving optimum results, as is the case with many non-linear solvers.
The newest version does not even run through, would you mind if I send the sheet to you in order to find the problem?
Firstly I appreciate your hard work, it is invaluable. Thank you
Secondly, is there a reference of the excel functions that are supported?
I am running into a problem with average(), that it’s not supported by the Bonmin and Couenne solvers. (I am doing a simple exponential smoothing problem with Holt method, optimizing for a minimum RMSE (Root Mean Square Error), making it a non-linear problem)
Sorry but we don’t have a full list of functions we support in our parsing-based solvers like Couenne; it is on our to-do list to document this. Nomad is available from https://www.gerad.ca/nomad/ We include this with some Excel support methods in the Dll file we provide. Hope this helps. Andrew
I can’t seem to get the new non-linear solvers to maximize an objective using the =PRODUCT() function. Do you know if this is supported? I’ve tried the manual calculations as well =(A1*A2*…) and no success. Do you have any guidance? Thank you kindly.
I don’t think we have support for PRODUCT yet (this should be possible to add if necessary), but A1*A2*… should be working.
Can you send through the sheet with this problem (and also the sheet using PRODUCT so I can have a look at adding support for it) to me at jdun087 at aucklanduni.ac.nz
Hi,
I have a model with time series where OpenSolver searches for optimal decision for each year. So I use a Quick Solve Function for each year in a loop procedure in VBA. The problem is that decisions for years been calculated without setting up the Solver Model (that is all except the first round in a loop) are far from optimal.
If I use loop without Quick Solve setting up the model takes too much time, as i have 15 years to “optimise” it is not convinient. Could you please advise how to fix such problem?
I have been using the CBC engine in Excel using opensolver add-in to solve basic price optimization problems, and I noticed that the model does not work correctly when percentages are specified as a constraint. In my test I priced the labor of 4 teams supplying a dozen types of worker to complete a task that required a fixed number of hours per category. My objective was minimum price, subject to the constraint that Team A would get >= 30% of the dollars, Team B >=20%, and so on. For some reason, the solution ignores the 30% constraint for Team A, but meets the others. If I express the constraint in dollar terms, not as a percentage, then it all works fine. I can send a simple file that illustrates this if sent an email.
Having formulated a model in OpenSolver, it now seems that it’s too big to run in a sensible time (estimated 17 days). I see SolverStudio is recommended to speed things up and for large problems like this. I have installed SS and GAMS but I have no knowledge or experience of the latter. Is there any way I can take my OpenSolver model definition and automatically translate it?
…I tried taking a smaller version of the model and running it against the Neos solvers to get AMPL code. I then tried pasting this into the SolverStudio window for the same version of the model and running this, again against the Neos solver. Wasn’t happy, giving an error like “a value was written to the sheet for data item “N304″ but this data item has not been defined in your spreadsheet…” Doubtless this is to be expected, but am looking for a way to recast model this quickly in a way which doesn’t require me to become fluent in GAMS overnight. (Have seen there are utilities to go from GAMS=>AMPL, not clear can easily go the other way around, but the fact that OpenSolver can generate AMPL code gave me hope of a starting point.)
A model can be slow because (1) it is large and takes a long time for OpenSolver to create the model’s equations from the spreadsheet, or (2) it is hard problem with binary or integer variables. Moving to SolverStudio will only help with the first issue; 17 days suggests you are facing the second problem, in which case you may need expect assistance in re-formulating the model to be easier to solve (i.e. “stronger” to use the jargon). You can also try making it smaller and seeing if small instances can be solved.
To answer your second question, we have an on-going research project to perform OpenSolver -> SolverStudio conversions, but at the moment cannot offer anything useful.
Hope this is of some help,
Andrew
PS: Get in touch again if you are looking for modelling expertise.
I am trying to solve a MILP where the decision variables need to be binary. When I run OpenSolver through VBA code using CBC, I get some 0.5 values in my solution. However, when I click the OpenSolver solve button, I get a feasible binary solution. I have turned off the option in Solver to ignore integer constraints, restarted, and resolved with no luck. I have also unsuccessively tried replacing the binary constraints with integer constraints. Do you know what might be causing my issues?
The first argument to RunOpenSolver specifies whether to enforce or relax integer constraints; have you set this correctly? It should be False if you want to enforce integer requirements. I’d check this first. Andrew
Thanks for this project. The installation of advanced Opensolver went flawless and I like the user interface. However, I was not yet successful in migrating a tested Excel NL model to OpenSolver. The only engine that I can run is NOMAD. Other NL solvers give an error message: “OpenSolver2.6.2 encountered a problem: Method Range of object_Global has failed (at line 5096) (at line 4840) (at line 7144)(at line 2816). Errnumber=1004
The model has around 10 variables. Some constraints are formulated as: $E$34:$I$34 4113 frmOptions.OptionsActivate Me
End Sub
After a quick scan through the posts I didn’t encounter similiar issues. I hope you can help.
Albert: Sorry it is not working for you. Are you able to send your spreadsheet to us at a dot mason at auckland dot ac dot nz? This is the easiest way for us to find the problem. Thanks, Andrew
Thanks for the more detailed description. This error message indicates a function (in your case stdev() ) that we don’t support when translating to get solver input files. (Nomad works as it simply calculates inside Excel.) The next version will give a better error message, but this won’t help make it work for you, sorry. You could write out stdev() as a summation instead of using the function. Hope this helps, Andrew
I downloaded OpenSolver -> extracted it -> did not change any paths, I ran the OpenSolver.xlam. Under the data tab the solver appears. But when I run the solve, it says cannot find cbc.exe in solvers folder. But under solvers->win32-> there is a file cbc.exe. Any suggestion on how to solve this?
Sorry it is not working. Can you please email to email hidden; JavaScript is required a copy of the text contents copied out of your About OpenSolver window. Also, can you please tell me where OpenSolver is installed… is it on a network drive of some description, or on your own hard disk? Andrew
I had the same problem. I did this and it work. Using Office 2007, Click the Microsoft Button, Click Excel Option, Click Add in, Click the GO button at the bottom of the windows, Check OpenSolver, Click Browse see the OpenSolver files are there in (C:\Users\mike\AppData\Roaming\Microsoft\Addins).
I was able to do this since I already ran OpenSolver’s setup file and I already move the files to C:\Users\username\AppData\Roaming\Microsoft\Addins
Hi all,
I hope you’re doing well. I’m very glad that you guys share such a wonderful tool as an open source.
I’m working on a school course project about optimization. I ran the model but it didn’t provide a feasible solution. I tried to find a feasible solution by myself and made sure that it satisfied every constraint I set. I’m wondering why the solver didn’t work.
And another question is that can the solver solve more than 10,000 variables problem?
Thank you very much. Look forward to your reply.
Best regards,
I have a small problem/question. I am solving a type of network flow model where some production points need to satisfy the demand of certain customer points and the solver does provide the solution but I was wondering why it isn’t being precise when selecting the amounts necessary to meet demand, meaning it’s wrongly equating values: 2,637.8 and 2,638 or 1,918.4 and 1,918 etc. and when these represent tons (like in my case), every little bit matters. The result is the same regardless of whether I use a >= or = condition (to meet or exceed demand). Can you please explain and help?
Perhaps your OpenSolver solver files (eg cbc.exe) are in locations you do not have any permission to run them from? Please send us the contents of your About… window’s text box. The exact error message would help; please just copy it from the error message window using Ctrl-C.
Thanks for more detailed error report. Please see my comments below about possible fixes… we will check to see what the cause could be. Can you tell is what circumstances made this error occur?
Sorry for again being inaccurate, but to give accurate values I will have to run the model again to take down the values. My model has about 27000 variables, many binary but some integer as well. It is a production scheduling model. I have struggled quite a while to find a number of errors I made in the model as it previously went straight to giving a no feasible solution after the setting up phase. At the moment is still takes about 1 hour 20 minutes to set up the model and then a further time that I have not observed to try and solve the problem. The log file shows 167280 seconds, but I know it didn’t take that long as I left it overnight and checked in the morning to find the error. Thus the model was solving for less than 12 hours. I can send the log file and the model to you if you would like.
I also tried to run the model in both CBC and the NEOS using CBC solver engine options and both gave the same result, the errnumber 70. I’m not sure if this answered your question about the circumstances for the error to occur. Please indicate if there is some other specific information you require.
Pierre. This seems to be a file being left open, perhaps because Excel or OpenSolver crashed? Can you please restart (which will close the files) and try again. If it still fails, can you please send us your model. Thanks, Andrew
Thank you, what a great tool! I came across Opensolver via a Google search after struggling unsuccessfully for a few hours with Excel’s own Solver add-in. Opensolver achieved in literally 2 seconds what I wasn’t able to do with the other tool.
It’s particularly great that it’s all open source in VBA so I can fully integrate it with my own macros.
Sorry about this issue. We will look into the cause – we have some suspicions. Please keep an eye out for the next release which will hopefully fix this.
Hi, I have a sheet with quite a few constraints. A previous version of OpenSolver could run through this problem but not giving optimum results, as is the case with many non-linear solvers.
The newest version does not even run through, would you mind if I send the sheet to you in order to find the problem?
Regards
Thinus
What is the error message given?
Firstly I appreciate your hard work, it is invaluable. Thank you
Secondly, is there a reference of the excel functions that are supported?
I am running into a problem with average(), that it’s not supported by the Bonmin and Couenne solvers. (I am doing a simple exponential smoothing problem with Holt method, optimizing for a minimum RMSE (Root Mean Square Error), making it a non-linear problem)
Thirdly, is there a way for non programmers to get the OpenSolverNomad.dll (made by https://github.com/OpenSolver/OpenSolverNomad I assume)?
Best regards
Mads
Sorry but we don’t have a full list of functions we support in our parsing-based solvers like Couenne; it is on our to-do list to document this. Nomad is available from https://www.gerad.ca/nomad/ We include this with some Excel support methods in the Dll file we provide. Hope this helps. Andrew
I can’t seem to get the new non-linear solvers to maximize an objective using the =PRODUCT() function. Do you know if this is supported? I’ve tried the manual calculations as well =(A1*A2*…) and no success. Do you have any guidance? Thank you kindly.
I don’t think we have support for PRODUCT yet (this should be possible to add if necessary), but A1*A2*… should be working.
Can you send through the sheet with this problem (and also the sheet using PRODUCT so I can have a look at adding support for it) to me at jdun087 at aucklanduni.ac.nz
Hi,
I have a model with time series where OpenSolver searches for optimal decision for each year. So I use a Quick Solve Function for each year in a loop procedure in VBA. The problem is that decisions for years been calculated without setting up the Solver Model (that is all except the first round in a loop) are far from optimal.
If I use loop without Quick Solve setting up the model takes too much time, as i have 15 years to “optimise” it is not convinient. Could you please advise how to fix such problem?
Are you able to send me a sheet and a full description of what you are doing? This shouldn’t be happening
Email is jdun087 at aucklanduni.ac.nz
I have been using the CBC engine in Excel using opensolver add-in to solve basic price optimization problems, and I noticed that the model does not work correctly when percentages are specified as a constraint. In my test I priced the labor of 4 teams supplying a dozen types of worker to complete a task that required a fixed number of hours per category. My objective was minimum price, subject to the constraint that Team A would get >= 30% of the dollars, Team B >=20%, and so on. For some reason, the solution ignores the 30% constraint for Team A, but meets the others. If I express the constraint in dollar terms, not as a percentage, then it all works fine. I can send a simple file that illustrates this if sent an email.
Yes please, send the file to jdun087 at aucklanduni.ac.nz
Awesome code, thank you so much.
I would like to add in 10,000 simple constraints into the model (Quantity in solution <= Available), is there a way to cut and paste these in?
Add them as 1 block of constraints, with the left hand side being a range, and same for the right hand side. Andrew
Hi
Having formulated a model in OpenSolver, it now seems that it’s too big to run in a sensible time (estimated 17 days). I see SolverStudio is recommended to speed things up and for large problems like this. I have installed SS and GAMS but I have no knowledge or experience of the latter. Is there any way I can take my OpenSolver model definition and automatically translate it?
…I tried taking a smaller version of the model and running it against the Neos solvers to get AMPL code. I then tried pasting this into the SolverStudio window for the same version of the model and running this, again against the Neos solver. Wasn’t happy, giving an error like “a value was written to the sheet for data item “N304″ but this data item has not been defined in your spreadsheet…” Doubtless this is to be expected, but am looking for a way to recast model this quickly in a way which doesn’t require me to become fluent in GAMS overnight. (Have seen there are utilities to go from GAMS=>AMPL, not clear can easily go the other way around, but the fact that OpenSolver can generate AMPL code gave me hope of a starting point.)
Thanks for any suggestions you have…
A model can be slow because (1) it is large and takes a long time for OpenSolver to create the model’s equations from the spreadsheet, or (2) it is hard problem with binary or integer variables. Moving to SolverStudio will only help with the first issue; 17 days suggests you are facing the second problem, in which case you may need expect assistance in re-formulating the model to be easier to solve (i.e. “stronger” to use the jargon). You can also try making it smaller and seeing if small instances can be solved.
To answer your second question, we have an on-going research project to perform OpenSolver -> SolverStudio conversions, but at the moment cannot offer anything useful.
Hope this is of some help,
Andrew
PS: Get in touch again if you are looking for modelling expertise.
I am trying to solve a MILP where the decision variables need to be binary. When I run OpenSolver through VBA code using CBC, I get some 0.5 values in my solution. However, when I click the OpenSolver solve button, I get a feasible binary solution. I have turned off the option in Solver to ignore integer constraints, restarted, and resolved with no luck. I have also unsuccessively tried replacing the binary constraints with integer constraints. Do you know what might be causing my issues?
The first argument to RunOpenSolver specifies whether to enforce or relax integer constraints; have you set this correctly? It should be False if you want to enforce integer requirements. I’d check this first. Andrew
Thank you so much, Andrew!
Hello Andrew,
Thanks for this project. The installation of advanced Opensolver went flawless and I like the user interface. However, I was not yet successful in migrating a tested Excel NL model to OpenSolver. The only engine that I can run is NOMAD. Other NL solvers give an error message: “OpenSolver2.6.2 encountered a problem: Method Range of object_Global has failed (at line 5096) (at line 4840) (at line 7144)(at line 2816). Errnumber=1004
The model has around 10 variables. Some constraints are formulated as: $E$34:$I$34 4113 frmOptions.OptionsActivate Me
End Sub
After a quick scan through the posts I didn’t encounter similiar issues. I hope you can help.
regards,
Albert
Albert: Sorry it is not working for you. Are you able to send your spreadsheet to us at a dot mason at auckland dot ac dot nz? This is the easiest way for us to find the problem. Thanks, Andrew
Thanks for the more detailed description. This error message indicates a function (in your case stdev() ) that we don’t support when translating to get solver input files. (Nomad works as it simply calculates inside Excel.) The next version will give a better error message, but this won’t help make it work for you, sorry. You could write out stdev() as a summation instead of using the function. Hope this helps, Andrew
Hello,
I downloaded OpenSolver -> extracted it -> did not change any paths, I ran the OpenSolver.xlam. Under the data tab the solver appears. But when I run the solve, it says cannot find cbc.exe in solvers folder. But under solvers->win32-> there is a file cbc.exe. Any suggestion on how to solve this?
Thank you
Sorry it is not working. Can you please email to email hidden; JavaScript is required a copy of the text contents copied out of your About OpenSolver window. Also, can you please tell me where OpenSolver is installed… is it on a network drive of some description, or on your own hard disk? Andrew
I had the same problem. I did this and it work. Using Office 2007, Click the Microsoft Button, Click Excel Option, Click Add in, Click the GO button at the bottom of the windows, Check OpenSolver, Click Browse see the OpenSolver files are there in (C:\Users\mike\AppData\Roaming\Microsoft\Addins).
I was able to do this since I already ran OpenSolver’s setup file and I already move the files to C:\Users\username\AppData\Roaming\Microsoft\Addins
Hi all,
I hope you’re doing well. I’m very glad that you guys share such a wonderful tool as an open source.
I’m working on a school course project about optimization. I ran the model but it didn’t provide a feasible solution. I tried to find a feasible solution by myself and made sure that it satisfied every constraint I set. I’m wondering why the solver didn’t work.
And another question is that can the solver solve more than 10,000 variables problem?
Thank you very much. Look forward to your reply.
Best regards,
Jack
Hello and thank you for this great software!
I have a small problem/question. I am solving a type of network flow model where some production points need to satisfy the demand of certain customer points and the solver does provide the solution but I was wondering why it isn’t being precise when selecting the amounts necessary to meet demand, meaning it’s wrongly equating values: 2,637.8 and 2,638 or 1,918.4 and 1,918 etc. and when these represent tons (like in my case), every little bit matters. The result is the same regardless of whether I use a >= or = condition (to meet or exceed demand). Can you please explain and help?
Thank you!
I’d check the display settings for the cell values in the sheet… it is unlikely to be a solver error.
Hi Andrew
I am getting an error message when trying to run my model. It says Error at line 1800 (about). Errnumber 70. Permission denied.
What does Errnumber 70 mean? Can you give me any pointers on what to look for to resolve this?
Thanks
Pierre
Perhaps your OpenSolver solver files (eg cbc.exe) are in locations you do not have any permission to run them from? Please send us the contents of your About… window’s text box. The exact error message would help; please just copy it from the error message window using Ctrl-C.
Hi Andrew
Sorry I didn’t have the accurate line of the error before. Below follows the copied contents of the error message.
—————————
OpenSolver2.6 Error
—————————
OpenSolver2.6 encountered an error:
Permission denied (at line 2810)
Source = OpenSolver, ErrNumber=70
—————————
OK
—————————
Pierre
Thanks for more detailed error report. Please see my comments below about possible fixes… we will check to see what the cause could be. Can you tell is what circumstances made this error occur?
Hi Andrew,
Sorry for again being inaccurate, but to give accurate values I will have to run the model again to take down the values. My model has about 27000 variables, many binary but some integer as well. It is a production scheduling model. I have struggled quite a while to find a number of errors I made in the model as it previously went straight to giving a no feasible solution after the setting up phase. At the moment is still takes about 1 hour 20 minutes to set up the model and then a further time that I have not observed to try and solve the problem. The log file shows 167280 seconds, but I know it didn’t take that long as I left it overnight and checked in the morning to find the error. Thus the model was solving for less than 12 hours. I can send the log file and the model to you if you would like.
I also tried to run the model in both CBC and the NEOS using CBC solver engine options and both gave the same result, the errnumber 70. I’m not sure if this answered your question about the circumstances for the error to occur. Please indicate if there is some other specific information you require.
Thank You
Pierre
Pierre. This seems to be a file being left open, perhaps because Excel or OpenSolver crashed? Can you please restart (which will close the files) and try again. If it still fails, can you please send us your model. Thanks, Andrew
Thank you, what a great tool! I came across Opensolver via a Google search after struggling unsuccessfully for a few hours with Excel’s own Solver add-in. Opensolver achieved in literally 2 seconds what I wasn’t able to do with the other tool.
It’s particularly great that it’s all open source in VBA so I can fully integrate it with my own macros.
Thanks for the positive feedback.
Hi Andrew, when I try to use the “solve options” gives me an error. I’m using the OpenSolver 2.6.1 and Excel 2013 Spanish version.
Thank you!
Sorry about this issue. We will look into the cause – we have some suspicions. Please keep an eye out for the next release which will hopefully fix this.