### Welcome to the OpenSolver on-line help.

We hope that most of your questions will be answered on the Using OpenSolver page. However, if you still have a question, please post it below.

## Building Linear Programming models

Writing optimisation models that only use linear mathematical equations and inequalities is not easy. However, most of the time you want to build these “linear programming” models (and avoid non-linear models) because these are easier and more reliable to solve using packages such as OpenSolver. (OpenSolver uses CBC by default to solve these linear programmes.) Check out this Wiki page on linear programming.

The AIMMS folks have a great online chapter describing how to take non-linear requirements (local copy of pdf) and model them using linear programming. (Thanks to Stu Mitchell of PuLP fame for pointing out this resource.) The NEOS guide is also a great resource. If you still have a modelling question (e.g., how do I express this requirement using the rules for a linear program), please ask it on https://www.or-exchange.org/.

## Common Software Problems

Excel can have problems dealing with add-ons. Visit http://www.add-ins.com/how_to_repair_office.htm for a guide on fixing these.

### 1. OpenSolver not loading – July 2016 Office update (no error message)

In July 2016, MicroSoft released an update to Office 365 which prevents OpenSolver from loading unless the .zip file is “unblocked” before the files are extracted. (We expect this change to appear in other versions of Excel.) The symptoms are simply that OpenSolver does not appear in the Data tab; there is no error message shown. To fix this, delete your old OpenSolver files (but not the downloaded .zip file), right click the downloaded OpenSolver .zip file, choose Properties, and click the Unblock button which will show if the file is blocked. Once the file is unblock, close the properties dialog, and then un-zip the files and open up OpenSolver.xlam as usual.

### 2. Unable to find the external solver …

If OpenSolver reports this problem, then it could not find a solver file that comes with OpenSolver. Please check that you have extracted (i.e. un-zipped or uncompressed) all the files from the OpenSolver download.

### 3. The solver did not create a solution file.

If this error is reported, then it is likely that OpenSolver was unable to run the solver executable. This may happen if the OpenSolver files are in an ‘untrusted’ location, and so Windows will not let them be run. First check whether the solver is shown as found in the “About OpenSolver” form. If not, go to the folder identified in the “About OpenSolver” form and double click on this solver file. You should then see a command window open that gives the solver name and version information etc. If you don’t see this, then Windows should give you an error message to help you diagnose the problem. It may help to move the OpenSolver folder (and all the files it contains) into a location such as your Documents, or even into Program Files, and try again.

### 4. Solver crashes; OpenSolver reports no solution file

The files in the “Solver” folder provided with OpenSolver contain the actual solver code. These solvers are being continually upgraded as bugs are reported and fixed. If OpenSolver reports that no solution file was generated, then the solver may have crashed. This can happen on big problems when the solver runs out of memory, but also on smaller problems that happen to hit a solver bug. In such cases, you should check if a newer version of OpenSolver is available since we regularly update the solvers included with OpenSolver as they are released.

With CBC, a typical OpenSolver error for thois problem message might be: ‘The CBC solver did not complete, but aborted with the error code -1073741510’

If needed, you can download the latest version of CBC, Bonmin or Couenne from AMPL. Download the either the Windows or Mac version as appropriate. For Windows, get the 32-bit version if you have 32-bit Windows, or the 64-bit version otherwise. (The 64 bit version will solve much larger problems than the standard 32 bit version included in OpenSolver, so always choose this if possible. Right-clicking on Computer, and choosing Properties, will tell you if have a 32 or 64 bit system type.) Right click on the downloaded .zip file, choose Properties, and click the Unblock button if there is one. Then, open the .zip file, and drag the files into the right location in your OpenSolver Solvers folder (the win32, win64 or osx folder as appropriate), replacing the existing files. This may fix your problem.

**Report a CBC bug**

If after installing the latest version of CBC, CBC still crashes on you, then you may wish to report a bug. Please be sure to include the .lp file produced by OpenSolver; you can open this file using “View Last Model .lp File”. CBC bugs can be reported by either:

- The online CBC TRAC bug reporting system (which you will have to register for), or
- The CBC mail list

**Switch from CBC to Gurobi**

Since August 2014, OpenSolver has also supported solving models using the Gurobi solver. You can obtain a copy of this solver at no charge if you are at a university. Gurobi is faster and more stable than CBC, and comes highly recommended.

### 5. Repairing Excel

Sometimes it is useful (if all else fails) to repair your Office installation:

**Windows**

See these Microsoft Office 2010 and later instructions (or in Excel 2007, use Excel’s application menu, then Excel Options, then Resources, then “run Microsoft Office Diagnostics”). You might also want to look at http://support.microsoft.com/kb/166273, which talks about this.

**Mac**

Unfortunately there is no easy way to repair the Office 2011 installation. The best option is to follow these instructions to completely uninstall Office. You can then install Office again from scratch to get a clean version installed.

### 6. ‘Can’t find project or library’ : Missing Reference Errors

OpenSolver uses the “RefEdit” control, which we need but which can cause problems because of missing references caused by version changes. To check for missing references, using the VBA editor (which you access using Alt-F11), select OpenSolver (openSolver.xlam) in the Project window, choose Tools… References, and look for a “Missing:” line. If you see this, un-check that line, close the dialog, and choose File … Save OpenSolver.xlam to update the file. If this doesn’t help, try the above steps for repairing Excel.

**7. Error 70: Unable to delete the file: C:\Users\XXXXXX\AppData\Local\Temp\log1.tmp**

This errors happens if Excel crashes while OpenSolver is running, leaving log1.tmp open. Please restart your computer, which will close this file, and try again.

**8. OpenSolver was working on my Mac. Then I updated to Excel 16.16.7 (released late 2018) and OpenSolver stopped working. What do I do?**

We have had numerous users report this issue. We are looking for a Mac user with VBA experience to help us diagnose this problem. Please contact us at email hidden; JavaScript is required if you can help.

**9. OpenSolver does not work in Excel installed from the ****Windows Store**

Windows Store versions of Office applications do not support Add-ins such as OpenSolver; see this link, or this support page for another add-in, or this technical article from Add-in Express. This Dell article explains a little more. If you have a Windows Store version, it will display this in the Excel Version information, such as in the following example:

Version 1807 (Build 10325.20082 Microsoft Store)

The solution is to un-install Excel, and install the click-to-run versions, in which case the Excel version will change from ‘Microsoft Store’ to ‘Click-to-Run’. Standard installs (meaning not Office 365) don’t have this issue.

Hi. Why does the entered data disappear? What can I do about it? Thank you.

Hi. When entering data in the sidebar, the previous entered data disappears. I bring in Variable cells, the Objective cell disappears. Entering Restrictions, the Variable Cells disappear. What can I do about it? Thank you.

Hello,

Is there an option to have multi-start. I am running an optimization model but I want to access if there is another global optimal solution if we take different starting points. That option is available in the original excel built-in solver

If you are solving an LP or IP to optimality, then you are guaranteed to find an optimal solution. Multistart is normally used by heuristics which cannot offer this guarantee. Andrew

Hi, I’m using OpenSolver in an Excel spreadsheet where I’ve written the names of around 20 people and am using a model to match them to around 15-20 tasks. The people are matched based on a grid of numbers representing how much each person wants to do each task on a scale from 1-5. I’m optimizing a variable representing the total amount of “satisfaction”, which is higher for each person if they’re assigned tasks that they have a high preference number for.

My question is related to building such a model using external code. I would like to build a GUI (preferably in Python using PyQt5) where a user can enter the above information, and then behind the scenes, an Excel file will be generated along with the OpenSolver model, the model will be solved, and then the GUI will display the results. I have been able to fairly easily generate the Excel file using Python’s openpyxl module, which allows you to generate and edit Excel files from a Python script. I’m wondering if anyone here knows a way to generate and solve a linear programming model in Excel from a script (preferably Python) using OpenSolver. Any advice or resources related to this would be greatly appreciated. Thanks in advance for your help!

Hello all!

Can you help please with this error?

OpenSolver 2.9.3 encountered an error:

Unable to run the external program: OpenSolver2.9.3_LinearWin\Solvers\win64\cbc.exe” -exit

Error 5: Access is denied.

Looking into the error log, I saw it could be a permission issue, which is not exactly the case since I am the adm of the machine. I have already tried to reinstall, change the folder’s location, and it did not work. Can you please help me with possible solutions?

im using office 2021 and when i click to solve the error appears : patch/file access error

how can i fix it?

No Gurobi installation was detected. The value of %GUROBI_HOME% was not set.

Unable to find Bonmin (‘bonmin.exe’). Folders searched:

Unable to find Couenne (‘couenne.exe’). Folders searched:

Unable to find NOMAD (‘OpenSolverNomad.dll’). Folders searched:

You have probably not installed the advanced version… Andrew

Hello,

I use OpenSolver to determine job sequencing & allocation over machines/weeks. The production volume of up to 250 SKUs must be spread over 3 resources and 6 weeks. The solver uses integer numbers to allocate these volumes (= 4.500 decision variables, theoretical max. Currently using 2 resources, 4 weeks & +/- 100 SKUs).

Depending on the products which have to be produced in each resource-week, the buildover time (PDT) of the resource varies & is calculated using Excel logic. As a first set-up, solver is to find optimal decision variables to minimize total time (= production time + buildover time (called PDT)) .

When optimizing, the optimizer does only take into account the production time to fulfill the time constraint, and does not model/take into account the related PDT, giving outputs such as:

Hours used = 155h production time + 30h PDT time = 185h total time < 156h time constraint.

When I check the log file containing the model, I see the PDT constraints are not modelled (probably because of using FILTER – logic, which has to be re-evaluated with every new attempt of the decision variables).

QUESTION:

– It seems like the only option to solve my model is to have the Excel sheet recalculated, for each of the tried combination of decision variables. Is this something which can be obtained, and if so, how?

Your help is greatly appreciated!

Hi, is there a limit to the number of variables? I am using opensolver to select products and I have more than 200 variables. On startup it only selects me products from the beginning of the model even though those products should be selected.

There is no limit. Maybe that is (one of) the optimal solutions that you are finding? Andrew

hi,

I just installed opensolver on my Mac, but it doesn’t work, as i can’t open model to input data. i tried to delete the file and although i emptied the trash, it didn’t disappear. i don’t know what to do new with it. should i download another one? i actually downloaded another one but when i clicked on the open solver file, it said that i can’t open the same file at the same time. so can u advise me on what to do next, please?

Hello! I was wondering if I could receive some help on what is likely a relatively easy issue.

So far, I have not yet been able to use the solver. No matter which engine I try, I always get an error message that says the range of my worksheet is incorrect. I don’t know why I keep getting this message, as all entered values are definitely correct, and was just wondering if I could get some help.

Thank you!

As requested by email, please send a screenshot of the error message to email hidden; JavaScript is required Cheers, Andrew

Hello everyone,

I have a question regarding the update mechanism in OpenSolver. Is there an automatic update feature? I want to make sure that no new version is installed automatically after the release of a new OpenSolver version. Is it only possible to update manually?

Hi Maxim, I believe there is no auto update. However, OpenSolver should detect if there is a more recent version available to download, and may notify you if there is. John

Hi John et al,

I have encountered some issues using the OpenSolver. When I ran the OpenSolver, I receive the error message:

“The cell ‘F4’ in constraint ‘F4 <= 0.99' has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!).

This can happen if your model is nonlinear, or if you have made an error when constructing the model. Please fix this and try again."

As a sanity check, I reduced the number of variables used so I could run the optimization through the standard Excel solver, and that worked. When I used the reduced variables in OpenSolver again, though, the exact same parameters returned same error message as above. I do not believe that the problem is nonlinear. Have you seen this issue before, and if so, are there easy solutions?

Thanks and happy holidays!

Scott

Hi Scott, I sent you an email inquiring about your issue. We can discuss particulars on there and then a helpful response will be posted here for others having the same issue. Happy New Year 🙂 John

Edit: For others with this issue – this instance of the error occurs when attempting to use a linear solver on a non linear model. I would suggest having a look into ratio constraints, and how to make them linear. Otherwise, you may use a non-linear solver. Cheers, John

Hello,

I use a very basic linear programming model with OpenSolver 2.9.4 and cbc. The model is running but one of the decision variables calculated by cbc goes to only a single decimal (Excel solver is using 8 decimals for that same decision variable in comparison) and this in not accurate enough to respect the equality constraints that I have set. Yet OpenSolver presents the solution as optimal. My precision setting is set to 0.000001. It must be something very basic and simple to fix I am sure but this has me stumped. Searched the Help posts high and low but could not find any similar issues, any pointers would be greatly appreciated. I can send the model if needed.

Another question is if the latest versions of cbc win64 on AMPL are still compatible with OpenSolver 2.9.4? I have tried the oldest (20221208) and newest available (20231117) and I can’t get them to work when I swap with original cbc.exe packaged with OpenSolver 2.9.4

Hi Francisco,

It would be great if we could have the model off of you, so we can have a look at what is going wrong. I will contact you with an email to send it to (the help email is not staffed over Xmas 🙂 ).

Edit:

Please note that the latest AMPL version of CBC (2.10.10) seems to work differently from the OpenSolver released version of CBC (2.9.4), and takes NL files as input (whereas OpenSolver expects it to take LP files), and the run command is different. The version downloaded from the CBC GitHib (2.10.11) works similarly to 2.9.4 so seems to work with the current version of OpenSolver. The above are the current options for using more recent versions of CBC, before the next OpenSolver release.

John

After receiving your spreadsheet, it seems that there are some differences between the values intended, and those stored in the model file. Therefore, CBC is solving a very slightly different problem – causing these discrepancies between the expected values of decision variables, and that calculated by the solver.

This is due to some error introduced by the formulae used. For future reference, have a look into the following sources of floating point arithmetic error: roundoff error, absorption error, catastrophic cancellation, truncation error. These types of errors can compound when using complex formulae, creating the inaccurate values in the model file.

Simplifying your formulae, I can improve the precision greatly, hopefully to an acceptable standard.

Cheers, John

I use OpenSolver to optimize the assignment of customers to warehouse locations for a given number of warehouse locations based on the shortest distances (linear model). Because there are many customers and many potential warehouse locations, the number of variables and therefore the computing time is very high.

However, many assignments are nonsensical due to very long distances between customers and potential warehouses, so that corresponding assignment variables do not have to exist at all.

What options are there in OpenSolver not to create variables for all warehouse-customer combinations? Is it possible to reduce the variables to previously defined meaningful warehouse-customer assignments?

Hi Cervus,

It sounds like you have a classic transportation/transshipment problem, and you want to prohibit some routes. There is no option for this in OpenSolver, and comes down to how you formulate your LP. If you want to prohibit some routes:

– you could just not include them as decision variables.

– you can set the cost of a unit of flow on those arcs to be incredibly large.

– you can add a constraint setting the flow to be 0 in any case.

Interestingly, you may consider one option to be nonsensical, but could be determined to be part of an optimal solution – it is sometimes difficult to know.

Hope this helps,

John

Hi John,

Thank you for your quick reply.

I think you’ve captured my problem. In a classic transport problem, I have a lot of warehouse-customer combinations, for each of which I have defined binary decision variables. Now I would like to reduce the number of binary variables because various combinations do not make sense.

You suggested 3 solutions. Setting costs of non-useful arcs to be incredibly large or adding a constraint to set the flow to be 0 are good ideas. I think it could help reduce computing time.

I don’t quite understand the third suggested solution. I should not include non-useful arcs as decision variables.

In Excel I need to define a cell range for variables, e.g. A1:Z2000. These are 52,000 decision variables for potential warehouse-customer combinations. How can I tell OpenSolver to exclude defined, non-useful decision variables from this cell range?

Oh so yours is more of an assignment problem. Ah yes, that first solution would definitely depend on the size of your problem. It would be infeasible to try and manually exclude certain decision variables out of a range of 52000. Again, I do not think OpenSolver has this built in functionality – we can consider adding it in.

I think your best option is to constraint prohibited assignments to be 0.

– Some solvers have a “presolve” option. If you have constrained prohibited assignments to zero, “presolve” will essentially remove that as a decision variable and fix the value of that cell before solving – this can reduce the solution time, and should essentially have the same effect as not including the prohibited assignments as decision variables. To simplify this method further, you can create a cell which contains the sum of all of the prohibited assignments, and set that equal to zero as a constraint, forcing all of those assignments to be 0 (works with your binary constraint).

– If presolve is not on by default, add presolve as an option into the solver, write “presolve” in one cell and “on” in the cell directly to the right. Then go to Model > Options > Extra Solver Parameters Range and enter those two cells as a range. This will pass the presolve option to the solver you are using – check your solver has that option first.

Let me know how it goes,

John

Hi John,

Your tips are very helpful.

I’m using the CBC Engine to solve my assignment problem. There I can use the Presolve option you mentioned. This shortens the computing time to solve the problem.

Unfortunately, the first phase “Setting Up Problem” in OpenSolver still takes a very long time until the problem is processed with all variables. In this respect, it would of course be helpful not to define all binary variables for meaningless warehouse-customer assignments at all, but to force them to be 0 via constraints.

I would of course be very grateful for a tip on how I can speed up the phase of “Setting Up Problem”. But the tip with the Presolve option was already very good. Thank you very much.

Hi Cervus, I think CBC has presolve on by default. In any case this helps get rid of the unnecessary decision variables (but this probably increases the set up time). Unfortunately, I am unsure how you could really speed up the building process on your end, unless you can simplify your problem or remove overly complex constraints? Have a look into OpenSolver’s “Quick Solve”. It can be great for solving the same, or a slightly altered problem multiple times in the same session. With Quick Solve, OpenSolver should not need to rebuild the model from scratch each time, and can focus its energy on solving. Cheers, John

I am trying to perform a calculation for a linear programming formulation that includes a binary decision variable. I’ve gotten the message that the presence of the binary/integer constraints affect the ability of the solver to produce the sensitivity analysis. I’ve looked into removing that constraint and also doing the Solve Relaxation method, but I started running into a different problem. Once I started exploring that avenue the program started being unable to complete generating the solution and I was given lines like these on the crash info sheet that it provided:

Error -2147220504: The CBC solver did not complete but aborted with the error code -1073741819.

No Gurobi installation was detected.

Unable to find Bonmin (‘bonmin.exe’).

Unable to find Couenne (‘couenne.exe’).

Unable to find NOMAD (‘OpenSolverNomad.dll’).

I have downloaded some of these files, extracted them, and saved them with the OpenSolver extracted files but the program doesn’t seem to be locating them. It seems like the sensitivity analysis can’t be produced without being able to locate at least some of these materials. I’m unsure if I’m doing something wrong with setting up the extra materials for OpenSolver to be able to locate and use the files needed. If anyone can help me understand what I’m doing wrong, I’d appreciate it and help me figure out what I need to adjust for the sensitivity analysis to be able to be produced. Any help would be appreciated great and thanks to anyone who takes the time to consider my problem. I’m fairly new to the program and still trying to figure out how to navigate it.

Hi Andrew,

From the error message it looks like you are running CBC as your solver anyway, so it wouldn’t matter if the other solvers could be found.

OpenSolver seems to work fine with binary/integer constraints, and removing/ignoring them should allow you to generate a sensitivity analysis. It is most likely an issue pertaining to your particular linear program. Could you please send the spreadsheet or a similar spreadsheet with the same error to email hidden; JavaScript is required?

Cheers, John

Hi Andrew,

I have looked into your error, and it is coming from the CBC solver, giving a result – “Linear relaxation infeasible” (this, and other extra helpful error info can be found by following OpenSolver > View Last Solve Log File).

It appears that your error occurs when both: the solver cannot find a feasible solution when relaxing integer constraints, AND you have checked the “Output Sensitivity Analysis” box. Your error does not occur when one or both of the conditions I just mentioned is not met.

Edit:

But the thing to check in your case: Is your problem with integer constraints feasible to start with? (LPs are easier to solve than IPs so if the LP is infeasible, the IP should be too). I reran your program (with sensitivity analysis unchecked) and it returned infeasible. It seems that the error message: “Could not get sensitivity analysis due to binary and/or integer constraints” does not say that the IP is feasible/infeasible, just that the CBC solution has been returned to the sheet. This is something we can update in the next version.

Cheers, John