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
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,
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
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