OpenSolver for Excel
The Open Source Optimization Solver for Excel
We welcome your comments on OpenSolver.
I was curious to try your open solver in Excel, so I downloaded the advanced version (2.8.6 advanced for Windows). Naturally I tested it out on one of my more complex calculations that uses a successive approximations algorithm to solve an equation (so I have built my Excel spreadsheet with an intentional circular reference and enabled iteration). The first thing Open Solver gave me was a warning message that “iterative calculation mode is enabled and may be interfering with the initial calculation. Would you like to try disabling iterative calculation mode.” Which brings me to my first question about Open Solver — Does it have support for iterative calculations like I am using, and as a new user I don’t yet know where to find the toggle that enables such support, or does it not support such models at all?
The idea of linear/non-linear programming is that the equations are expressed via the model, not by circular references on the sheet. So, no, in general we do not support circular references. However, that said, the Nomad solver may be able to handle circular references as it never builds its own equations; you would have to try it. Hope this helps, Andrew
Hi, I’m using OpenSolver from Google Sheet.
It works on 2017/09/30 when I was running against one specific problem, and gets the result successfully in 2 mins. But when I’m re-running the same problem without any change today (2017/10/09). It fails to return any result without any error/warning message after waiting for even 15 mins, and no progress bar shows, except the sidebar area is grayed out.
I suspect the OpenSolver server has some issues and some callbacks on the client side are not called for some reason which are blackboxes to me. Any one know whether there’re any issues at server side of OpenSolver, or any other issues that might cause this failure? Or can I run the solver purely on client to workaround the silent failure?
Sorry it is not working. Google often change things. Please let us know if this problem persists, and we will look into it. Feedback from other users would also be good to get. Thanks, Andrew
Thanks for replying.
I tried again today and the issue is the same.
One of my friends also tried from her side yesterday and today, and the same problem was met.
Please help take a look and try to reproduce from your side.
We have now looked into this. Everything is working for us on the test problems; all three solvers are running without issue. Nothing obvious seems broken. If the solve script takes longer than 6 mins then it will hit Google’s timelimit which can with small probability cause the sidebar to become stuck in a disabled state. In that case you can just refresh the page and resume the solve. Perhaps you can send us the URL for an example sheet that is failing? Andrew
Thank you much trying to learn your product. I am trying to solve a problem where sometimes it may have 200 variable cells to choose from and sometimes it will be 125 for example. Is there any way to reference a cell that has a value in it instead of having to type 125 in the model for example instead of having S2:S125 it would have S2:(reference of a cell with 125 in it). I am using a VBA loop solving many problems so the time savings on the less options add up.
If you arw using VBA then write code to change the decision cells. See the VBA documentation on our Using OpenSolver page. Andrew
Hello, I understand that there’s limited support for Office 2016 for Mac but I’m hoping this issue might have a fix.
I’m running OpenSolver (2.8.6) on a Mac using Excel version 15.37. When I try to Solve a model using the NOMAD engine I get the following error:
“‘OpenSolverNomad’ can’t be opened because Microsoft Excel is not allowed to open documents in Terminal”
If I clear the error and then go to the folder where the engine is saved, double click the engine, it’ll open in Terminal, run, and successfully solve the model.
From what I gather this error is likely arising because of the Mac sandbox requirements. Not sure how to proceed and if there’s already a workaround to allow it to open the engine.
Sorry, it looks like things have changed again on the Mac. We will look at this, but it won’t be soon, unfortunately. Andrew
[My apologies if this becomes a duplicate post, I attempted to add this in the “Help” section and it didn’t show up, so I’m posting it in “Feedback”]
Hi Team OpenSolver,
Thanks for all of your efforts creating such a useful resource! I have a question on integer tolerances. I’ve used OpenSolver to create a production scheduling/location MIP model (determine the production location for each piece of demand to minimize lead time to customer, subject to capacity constraints, with penalty costs added for exceeding capacity). The decision variables for ‘production quantity’ are constrained to integers (discrete production process). The model isn’t behaving as I expected with regards to the “Branch and Bound Tolerance (%)” integer setting, as the returned values do not seem to be within the % tolerance of the continuous solution. For example, when I solve the continuous/relaxed model, the objective value being minimized is 3,613,245 (relatively large due to my penalty costs being added). However with OpenSolver set to 0.1% tolerance, the model quickly returns a solution of 8,384,771, or 132% larger than the relaxed solution. It doesn’t seem to be respective the 0.1% tolerance setting? I’ve played with the tolerance settings with similar results. I checked the “Last Solve Log” file, and saw that the tolerance is being set by the model properly: “ratioGap was changed from 0 to 0.001”. It also reflected the LP solution properly: “Continuous objective value is 3.61324e+06 – .04 seconds”. But later in the log shows: “Cbc0012I Integer solution of 8384771 found by RINS after 6986 iterations and 1239 nodes (31.55 seconds)”, then the next line is “Cbc0011I Exiting as integer gap of 1782 less than 1e-10 or 0.1%”. I’m not sure why it is interpreting that objective value as a 1782 gap and less than 0.1%, as compared to the initial continuous objective value of 3.61324e+06? Any thoughts on what could be impacting the integer tolerance?
I also created a simplified/small version of the model to test a similar structure to see if I could better isolate the error. I again encountered the model returning a solution outside of the specified integer tolerance % (slightly different behavior in the simplified model – it quickly returns a solution even when the tolerance is set to 0%, that has an objective value 4.2% larger than the continuous objective value). Any thoughts to point me in the right direction are greatly appreciated! FYI – if I try to solve this small model in Excel Solver with the same settings, it attempts to continue solving to try to find a solution within tolerance instead of quickly providing a solution that doesn’t meet tolerance (although note that it just continuously runs until I stop it since it doesn’t find an acceptable solution).
This sounds like a CBC issue. I suggest you raise this issue, and provide supporting files (which you can find using “View all OpenSolver files”), on the CBC support list. See http://opensolver.org/help/ for more information.
Thanks for the quick response Andrew. I posted this to CBC (https://projects.coin-or.org/Cbc/ticket/178) and received a reply. It turns out I was misinterpreting the ratioGap parameter as the gap between the returned solution and the LP relaxation solution. However, the ratioGap parameter actually represents the gap between the returned solution and the lower bound yielded by the branch and bound tree. Thanks.
intorduced binary variables …
When I save the model,,
The model has specified that a non-decision cell must take an binary value. This is a valid model,. But not one that open solver can solve
When I click solve button,
A range of cells are specified as bin or int that are not decision variables. Open Solver does not support this.
what is the problem here?
First let me thank the OpenSolver team, this piece of software is awesome! We use it for consulting at SimWell Technologies (Montreal, Canada).
I have found an issue with the QuickSolve initialization. And I also found a way to fix it by debugging the VBA.
Here’s the situation: I’m building a model which has some binary variables, and some contraints’ LHS are sums of binaries. The RHS is 1. Operator <=1.
The binary variables all are set to 0 before I start the initialization. (so the RHS of theses constraints are 0)
When the initialization occurs, there seems to be some values which have been temporary changed by the model building phase. However, the sheet does not get calculated once before starting the parameter analysis. In this case, the calculated values on the RHS of the constraint are now valued 8 (instead of the original 0). I'm guessing each binary value has probably been set to 1 at some point during model building…(and the LHS is a sum of 8 binary cells)
Therefore, when reading/storing all of the original LHS values, the values stored are not right (they are 0)
Then, when the code tests changing the parameter value in order to measure the impact on RHS, it recalculates the sheet. The same constraints' LHS are re-evaluated to 0 (instead of 8 just before) Therefore, the code THINKS there is a coefficient of 8 (or maybe minus 8, but it doesn't matter) for ALL parameters, while the constraint has nothing to do with the parameter (or any parameter for the matter).
Next, when I QuickSolve, it cannot find a solution because several constraints are messed up. (I checked the model.lp file)
In conclusion, I've added a line of code in function "AnalyseParameters" in the CQuickSolve Class module: Application.Calculate
Just before the current constraint values are read…
Let me know if you need an example where this issue occurs, I'll remove any customer-sensitive info from the model file and e-mail it to you.
Thanks, have a good day,
André Jacques, P.Eng.
—-Notes on other feature development—-
I'm thinking about making quicksolve "saveable", at least to adapt the code to make sure it works for our current project, when the user closes and re-opens the file. (maybe not totally flexible for all models one could build with OpenSolver).
After looking at the code, I'm thinking of maybe creating hidden sheets automatically to store all of the built model info into, and read it in just before QuickSolving.
Thanks for finding and describing this issue so clearly. We will fix it in the next release. Your code to save the Quick Solve data would be a very useful addition if you were happy to share it. Andrew
Andrew/Andre – Did you manage to add the ‘save’ feature to OpenSolver? I am facing a similar problem (large model, that takes a long time to parse but could be run from a saved quicksolve..)
If you did, I’d greatly appreciate trying it out too!
Not yet, sorry. Perhaps over our New Zealand summer we will make some progress. Andrew
Hello Akshay and Andrew,
Yes, I’ve managed to create some VBA which saves most of the info processed in the initialize quick solve in the Excel workbook. Then, when you redo the the initialize quick solve after re-opening the workbook, it works fine and saves most of the model building time.
I am not quite sure this code is robust enough to be included in an OpenSolver release. (and it makes your workbook file quite BIG)
Also, Andrew, am I allowed to distribute modified OpenSolver code, according to the GPL? I think i’d be willing to share this with Akshay, please let me know how I should proceed.
I’m having an issue with a Mixed Integer model. I’m trying to have my model choose which items to produce and then how much of a resource to use to produce the item (with the resource being apportioned in integer increments). Basically, I can run my model without any binary decision variables (ie. the model chooses at random which items to produce). But once I introduce binary decision variables – because eventually I will introduce constraints on the number of items that can be produced simultaneously – it suddenly becomes infeasible. At this point, all the model would be required to do is pair a 1 with each non-zero integer production value, but it seems unwilling to do it. Might this be a problem of model size? Or is there some known issue where OpenSolver does not like choosing both which AND how many?
In my personally created Excel sudoku solver model, I have 729 variables that I want to be 1 or 0. I constrained the variables to be =0 and also to be int (integers). The problem does not solve, however, and returns non-integers among the 729 variables. How can I fix this?
Ensure you have specified that the variables are binary valued. If it does not solve. then you need to find out why; is it infeasible? Andrew
Hello, When I run binary non-linear model using NEOS ,I get the error – NEOS was unable to solve the model because there was an error while running AMPL. Not sure what to do next. If you like I can send you a copy of my spreadsheet. Any help/suggestion would be appreciated.
You are probably using formulae that AMPL cannot understand. Please keep it to simple addition, multiplication, etc. We have a plan to try to report on these errors in more detail, but that is in the future, sorry. Andrew
I keep getting ‘Can’t execute code in break mode’ within the VBA error compiler when attempting to utilize the add-in. I’ve reviewed my Add-In settings, but can’t seem to get it working. Any next steps for me to resolve? Thanks for your efforts.
That’s unusual. Can I suggest you delete your current opensolver files and then unzip them again to get clean copies. Let us know how you get on. Thanks Andrew
I would like to convert Open Solver to a workbook so I can embed the VBA code. it does ask for a password. What is the password? I have version 2.7.1. thanks.
There is no password… you must have made a mistake. Andrew
Hi Andrew Mason,
May I know how to unlock the constraint of 300 variables sue to student version?
Please advise any price plan if necessary for consideration asap.
There are no limits. The software is free. Andrew
Google is interested in obtaining a license for OpenSolver for Excel.
Is it possible to get a copy of the End-User License Agreement (EULA) so we can review it?
Also, can you confirm if your software utilizes any SaaS connectivity to save data in the cloud? If any, please explain how it works?
Ravi. OpenSolver is released under the GPL and so is free for businesses such as Google to use and deploy. If you wish to change the OpenSolver VBA code (which is most unlikely given its use to build models, which can be done without code changes), then that’s fine for internal use. But if you redistribute the modified code you need to also share the modified source. Re cloud access, OpenSolver can use the NEOS cloud solvers or run locally with no cloud interaction. Hope Google finds OpenSolver useful. Andrew
OpenSolver 2.8.6 out of stack space. I have a large non-linear (Integer) model that runs out of stack space? What is the best way to solve this problem?
A patch has been submitted, but not yet merged, that should fix this. I cannot promise a release date though, sorry. In the meantime, I’d try SolverStudio (or the latest version of OpenSolver, if you have not already). Andrew
I have figured out that that program will run successfully the first time, then continuously give errors. If i restart Excel it will run properly. Just giving you some additional feedback to help with the fix … thanks for putting this together.
btw this appears to be an issue for all the solvers.
Recently, any new workbook I was opening on Excel 365 defaulted to manual calculation. The problem disappeared after I disabled the OpenSolver add-in.
So somehow there is something in OpenSolver that’s causing this behavior. Although this is not a very serious problem (can be solved in a couple of clicks), all those clicks do add up over a period of time.
This is fixed in the latest version (2.8.6)
Your email address will not be published. Required fields are marked *