OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
Dear OpenSolver Team,
I am really surprised by the great results of your work. I have been using OpenSolver for some time (large Stock Cutting problems) it works with good results.
I also noticed that a large part of the CBC.exe calculation is consumed by process of finding a possible solution. However, in my case, knowing the specificity of my problem, I can easily generate a feasible solution – being a good starting point. The question is whether and how to define the initial solution that will be used by OpenSolver (and later by CBC.exe -mips)?
Thank you in advance for your respose.
OpenSolver is able to look at the current solution in the spreadsheet, and if it is feasible, pass it to the solver as a starting solution. However, I don’t think we have this coded up for CBC (but it might be worth double checking this). We do this for Gurobi, so switching to Gurobi is an option. You can check the code for this by searching for InitialSolutionIsValid. We’d welcome any code changes you make to get this working with CBC. Cheers, Andrew
please, I have model in excel, which is linear programming (no MIP or IP). Can I use OpenSolver CBC solver for this? Do I get right result? I ask You, because in definitions it writes that CBC is for (MIP) and I need solver from LP. When I write easy example for CBC solver, it give me a result with decimal numbers in variables.
CBC also solves linear models with no integer/binary requirements. Andrew
Hello, can I ask how to still have the solution even though it didnt satisfy the constraint?
for example, I have matrix with supply and demand as a constraint. But the demand is greater than supply so it will never satisfy the model still I want to saw the distribution of this problem so I can make decision. I use google open solver because on excel didnt open at all even though its loaded to the excel.
I’m looking to see if OpenSolver or SolverStudio might be able to solve my investment problem.
I’ve set up an Excel spreadsheet with (6) investment categories, those being: Fixed Rate Mortgage, Variable Rate Mortgage, Kid 1 529 Plan, Kids 2&3 529 Plan, Dad’s 401K, & Mom’s 401K. In another column is the investment income. Each month’s investment income can be divvied up to each investment.
As a large-scale problem I’ve been able to use a (30) day trial of a very expensive commercial Solver to determine the monthly distribution. Takes a couple of hours to optimize but ultimately tells me where to put our monthly investment money.
Nothing against the commercial Solver but I would like to see if OpenSolver or SolverStudio could do the same or better optimize the problem.
Before I invest a bunch of time changing my Excel problem over to be OpenSolver-friendly does it sound like it is within the realm of OpenSolver or perhaps SolverStudio?
thanks for your feedback!
I’d have thought this was a linear (possibly binary) model. It should solve using CBC or via NEOS. Hope your get it going. Andrew
It’s been over a year since the (30) day trial expired but if memory serves it was non-linear. I’m using several Excel formulas such as IMPT, PPMT, DAYS, NPER, and FV. I’m assuming I need to get away from those first?
These formulae are not something we can convert (‘parse’) into a modelling language/optimisation form. These are non-linear. NOMAD can handle these Excel-specific formulae, but not the other non-linear solvers. But, there is probably a linear formulation with variables such as x_ij=1 if item is is sold in period j, etc. Hope this helps, Andrew
On google sheets, I open OpenSolver but it won’t let me enter any information. It worked great in the past but I haven’t used it in a month or so and this morning when I went to use it everything was shaded gray and not allowing any info to be entered.
I have not seen this, sorry. Have you tried removing and then adding back OpenSolver? Andrew
I have to say, I am truly admiring you guys.
I do not use the OpenSolver functions but your tool is to me like a real bible when it comes to understand how to optimise VBA code for excel. I’m currently working on a VBA project for my parents as they use Excel on a daily basis and could use automation in their work. I have to say, the ribbon gives me a headache trying to make it fool proof. I’ve been using your code as a example to help me go through the code wondering how you made it so responsive and fast to load. Having a well working add-in makes a big difference and I thank you.! It is so much better than digging into forums to find what I’m looking for. If you had a advice for a beginner in excel add-ins to achieve the same kind of work you do, what would it be?
I need to install 64Bit Excel (Office 2016). Will OpenSolver (2.7.1 or above) work with the 64Bit version? Or do I need to download alternative version of OpenSolver?
Same version does 32bit and 64bit Excel on Windows.
The installation directions for opensolver on windows 10 excel 2010 are missing a lot of steps. For example when you copy the files to the addins folder, should you copy the folder created by the unzip program, or the individual files. Once you’ve copied the files it would be helpful to have instructions as to how to install opensolver permanently (i.e. by going to files, options, . . .) Please make it step by step.
Phil, We’d welcome user input to make this process easier to follow. If you had the time to write something up, we’d love to add it to our instructions. By the way, you do not need to install OpenSolver but can just run it from your documents or temp folder. Installation so that Excel always shows OpenSolver is optional, and is one click away using the About OpenSolver box (without copying any files; it will continue to run from its current location). Hope this helps, Andrew. PS: Please never copy just the files; the OpenSolver folders and files need to stay together.
Your email address will not be published. Required fields are marked *