Hi,
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!
And what about by using macro? After look in OpenSolver API Reference I would expect that is possible use macro like:
Sub add_const
Opensolver.AddConstraint(Range(“XXX”), RelationINT, , , Sheets(“Sheet_A”))
End Sub
First – thanks a lot for a wonderful tool, 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.”.
We are still trying to resolve this. We have done all the obvious steps, but that has not addressed the problem. Sorry for the delay with this; it is very frustrating for us as well. Andrew
Hi Andrew.
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.
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”?
Any idea by when the transfer time between Google Sheets and OpenSolver will be drastically shortened?
Background:
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?
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:
Type mismatch”
BuildConstraintMatrix: Line 1305
BuildModelFromSolverData: Line 1186
RunOpenSolver: Line 0
Sorry it is not working. Can you please send the spreadsheet to email hidden; JavaScript is required? Please also send a copy of the text in the About OpenSolver screen. Thanks, Andrew
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.
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.”
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.
OpenSolver does not change any formulae. Perhaps Excel is changing how the formulae are being displayed? Could you email us a screenshot, and the text from About OpenSolver giving the Excel version etc, to email hidden; JavaScript is required . Thanks, Andrew
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.
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
Hi, Andrew!
May I know if OpenSolver provides function to download the model as lp file in Excel?
Thanks!
Yes; have a look under the OpenSolver menu… view last model file.
Andrew
Hi,
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:
Sub add_const
Opensolver.AddConstraint(Range(“XXX”), RelationINT, , , Sheets(“Sheet_A”))
End Sub
But it doesnt work.
Thankss very much for your help.
OH I found!
thanks anyway.
(
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 tool, 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?
Regards
Alexander.
Were you able to solve this issue? If so, can you share how? I am having the same problem.
We are still trying to resolve this. We have done all the obvious steps, but that has not addressed the problem. Sorry for the delay with this; it is very frustrating for us as well. Andrew
Hi Andrew.
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
Dear 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!
Jakub
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?
Background:
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:
Type mismatch”
BuildConstraintMatrix: Line 1305
BuildModelFromSolverData: Line 1186
RunOpenSolver: Line 0
using CBC 2.7.1
Sorry it is not working. Can you please send the spreadsheet to email hidden; JavaScript is required? Please also send a copy of the text in the About OpenSolver screen. Thanks, Andrew
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:
https://support.office.com/en-us/article/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460
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.
OpenSolver does not change any formulae. Perhaps Excel is changing how the formulae are being displayed? Could you email us a screenshot, and the text from About OpenSolver giving the Excel version etc, to email hidden; JavaScript is required . Thanks, Andrew
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?
D
Best regards
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