### 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.

Hello Andrew,

I was wondering… do you think it might be simple (or even possible) to work on a solution where your solvers would be used to solve VBA functions, without using the spreadsheet as a support e.g. all integrated in vba code ? This would require the solver to access vba modules and launch functions, playing with parameters and recovering outputs right ?

Thank you in advance, regards,

Antoine

The NOMAD solver could do this, and indeed can do it now if you use a UDF (user defined function) on the s/sheet. Andrew

Thank you for your answer (impressively quick). That is the point, I do not want to have the UDF on the sheet, I want to by-pass my worksheet objects for calculations and only use it as database. Let’s say I have a column with 100 point and I want to fit a exponential model on it. The goal would be to have a sub getting the 100 points, storing it in a array variable and calling the solver to find the best parameters of my exponential model. Then we can imagine than the sub will paste the 100 fit points in the next column and returns the parameters.

That should be possible with some VBA and possibly C coding to link Nomad to your VBA function. Look at the nomad class in our VBA source first. Please let us know how you get on, and share your code if that’s possible. Cheers, Andrew

Yes sure, I am going to do that. Thx for the tips. I keep you update.

Looks like the download site is down.

Seems up now. Andrew

Every time I click Solve or Save the model in OpenSolver, the entire Excel app freezes and I have to force quit, when I reboot Excel OpenSolver is no longer there and I have to re-enter the model.

Very odd. Mac or PC? If you post your Excel version and Windows version from the OpenSolver about box then other users can tell you if it works for them on the same version. Andrew

I am reading Data Smart by John W. Foreman, which includes exercises in Excel to go along with each chapter.

In chapter 1 he instructs people with Excel 2010 or more recent to install OpenSolver. For the exercise in chapter 2 we’re instructed to make sure “that the evolutionary solver is chosen.”

Where is the evolutionary option in OpenSolver?

We don’t have an evolutionary solver. NOMAD is probably our most similar solver. Andrew

Hi and thanks for a great product.

I am using the Couenne (non-linear) Excel addin with Win10.

I discovered some Constraints containing SUMPRODUCT aren’t complied.

Are the translations of SUMPRODUCT with more than 2 arrays supported?

I cannot remember but I suspect not. Andrew

Is the latest OpenSolverNomad.dll a parallel version?

Thanks.

From memory, no; it has to call back into Excel to evaluate each solution by recalculating the spreadsheet. I don’t know how to do this call-back in parallel. Suggestions from programmers are most welcome on if this can be done. Andrew

Hi, thank you guys for extending Solver beyond 200 decision variables – much appreciated.

However, I am running into the following issue. I have a highly non-linear problem to solve, and I have already tried the non-linear algorithms you offer – Bonmin, Couenne and NOMAD. However, they all seem to run into the same problem – ‘unknown formula’ 🙁 I am using standard array formulae in XL, specifically ‘TRANSPOSE’. It appears your code cannot parse/recognize it, though, is that correct?

Is there a way around this, and/or are you planning on supporting array formulae anytime soon? Thanks again!

Best,

-Nick

No, we cannot handle Transpose. This function is linear, and so is not needed in your model; if you can re-configure the sheet, then you might make progress with the solver. Note that NOMAD can handle anything (it should not give you an error), but is typically much less efficient. Andrew

All well and good – I have removed TRANSPOSE by redesigning my model. However, I still need to use MMULT – and there’s simply no way around that. Some of the engines I tried indicate a direct conflict with that function, while others fail with different types of errors. And NOMAD fails completely and immediately (while the others take a while).

Do you have any other suggestions at this stage? I can’t be certain, but I have a sense that OpenSolver should be able to solve large-scale integer optimization problems, in one way or another.

Replace your MMult with one sumproduct for each cell in the answer. Andrew

NOMAD should not fail with an error but may not give very good quality solutions as it has no derivative information. Andrew

Hello,

first of all thank you for Google Sheets addon – thought such solution could be found only in Excel!

I am trying to solve my investment portfolio and find best one based on Sharpe ratio.

So I have calculated Standard Deviation and Expected value for each item in my portfolio and then based on thees need to calculate weight of each item with maximum Sharpe ratio for the entire portfolio.

unfortunately I get the following error when trying to solve this:

“The objective cell appears to contain an error (eg #DIV/0! or #VALUE!). This could have occurred if there is a divide by zero error or if you have used the wrong function. Please fix this and try again.”

when I am trying to solve the same problem based not on Sharpe ratio but on maximum/minimum stdev or ev – everything is working fine

OpenSolver was working. I then downloaded the update to Excel (16.16.7) and now it seems not to be working. Any ideas?

We have recently become aware of this problem caused by the Excel 16.16.7 upgrade on a Mac. We are looking for help from someone with a Mac that is having this issue and is familiar with VBA code. Any volunteers? Andrew

I am getting an error saying:

OpenSolver 2.9.0 encountered an error:

Out of stack space

An error log with more details has been saved, which you can see by clicking ‘More Details’. If you continue to have trouble, please use the ‘Report Issue’ button or visit the OpenSolver website for assistance:

However, when I click on Report Issue, it says:

Couldn’t create an email to report the issue. Please try again, or send us the error.log file manually.

Any help would be appreciated!

You have a non linear problem I think and Excel is running out of memory trying to expand the formula. You could try a smaller model or a bigger computer. Sorry but not much else I can suggest until we upgrade the formula parser. Andrew

I’d like to add my mention to the fact that OpenSolver in Google Sheets is not working correctly, most of the input boxes are greyed out and have a little cross out icon while hovered over any of them.

It is working for me, once I give it permissions. I am running in Chrome. Andrew

Thanks Andrew, seems to be working fine now. I believe it was a problem where I had two accounts logged into Chrome and since I was only working through one, the other didn’t have permissions.

How exactly are you “giving it permissions”? I am also running it in Chrome (first time user) and my options are greyed out as well.

Thanks

I can not make googleeolver work at all, it is not letting me input any information into the fields. Please help!

The model I created is a ‘simple’ maximization problem and used to take only 5-30 seconds to complete. However, now it takes a weirdly varying amount of time, and sometimes doesn’t* complete (I stop it when it reaches >500 secs, because it solves normally between 5-60). What should I be looking for to troubleshoot this issue?

Integer/binary variables in a problem can make it hard to solve after just small changes in the problem data. Good luck, Andrew

Thanks, I am using binary variables, that may be why. It still doesn’t really make sense to me why the model sometimes runs pretty quickly and then takes forever on different constants.

Try reading up about branch and bound; that might help you understand the solve time differences. Andrew

Just to follow up in case anyone else has similar issues:

I was using a ‘Big M’ method for a multiple of the binary variables, and the ‘M’ value was much larger than it needed to be for my model. Changing that to be smaller helped constrain the variables and keep the branch and bound more reasonable, solving much more consistently.

Keeping those Big-M values small is always a good idea a the LP and IP solutions are more similar. Andrew

Actually, further question. If I leave the optimization script running (selected on the options menu), after each 100 (tested up to 700) nodes the best possible value doesn’t change, and if I set a time limit on the script I get a solution to the problem I have that doesn’t keep to the binary constraint, but works within the context of my problem. Does this make sense? Not sure how else to describe the effects.

That is pretty much what I would expect. Andrew

Hello,

When I want to update a constraint or add a new one, the following error appears:

“The RHS is marked as #REF!, indicating it has been deleted . Please fix and try again”

Do you have any suggestion on how to solve this issue?

Thanks,

Manuel

You have deleted a cell on the sheet that is used in the model. However, this should not get you “stuck”. Please confirm that the Edit Model window does not let you fix the problem. If you are stuck, then can you please send us the sheet to email hidden; JavaScript is required ? Thanks, Andrew

Regarding the OpenSolver for Google Sheets, I’ve just downloaded – the sheet selection bar and ‘current sheet’ button are greyed out (as is the objective cell field) and so I can’t get it to work! Any ideas?

Just coming here to report the same thing has been happening to me for about a week and a half now. I have tried disabling all extensions and running Google sheets in an incognito window to no avail. Current specs:

Google Chrome 71.0.3578.98 (Official Build) (64-bit) (cohort: Stable)

Revision 15234034d19b85dcd9a03b164ae89d04145d8368-refs/branch-heads/[email protected]{#897}

OS Windows 10

JavaScript V8 7.1.302.31

Good morning Andrews,

First I would like to thank you for developing those free solvers ;-).

I have some issues regarding the non-linear solvers.

My laptop is a Dell Inspiron 15 3000series Core i5 (maybe not optimal for those calculation?).

Excel is a 64-bits version.

My problem has 8700 constraints (inequalities) and 375 variables. Constraints are linear functions, objective to minimize is a sum of inverse functions (f=sum(1000xi/(1+1000xi))) with xi the variables.

First, I’ve tried with Couenne on my laptop, but the solving time exceed 24 hours, so I aborted. Is it normal for such a problem?

Second, I’ve tried with Couenne with Neos, but calculation bugged saying that URL is not valid. What is the issue with Neos server?

Thank you for your reply.

Regards

Max

Extracting the model’s equations from the sheet can be slow; the model would be much faster using http://SolverStudio.org . I suggest you start with a small example using OpenSolver to test the ideas, and check it does what you want, before scaling it up. Also, that objective seems very strange to me, and numerically troublesome as each term is very close to 1; I suggest you try to linearise it, if possible. Andrew