**SolverStudio 0.6 2014.04.07 released, now with Excel 2013 support **SolverStudio is a free alternative to OpenSolver that is better suited to larger problems. SolverStudio 0.6 is now available, with lots of fixes and improvements, including Excel 2013 compatibility. Available as a free download, SolverStudio lets you use Excel to edit, save and solve optimisation models built using modelling languages such as the Python-based PuLP, AMPL, GAMS, GMPL, COOPR/Pyomo and Gurobi’s Python interface. The latest release allows GAMS and AMPL modesl to be solved in the cloud using the excellent free NEOS servers. The SolverStudio interface is fully Excel-based, with the model being edited and run from Excel and stored inside the Excel file. This approach provides a much better modelling solution for complex optimisation problems. Check out the screen shots to see how it works. SolverStudio is much better and faster for large problems. However, OpenSolver is still a great tool for simpler models, or spreadsheets that must be compatible with the built-in Solver.

**COIN-OR Cup Winner:** We are pleased to announce that OpenSolver is the winner of the 2011 INFORMS COIN-OR Cup sponsored by IBM. Thanks, COIN-OR, for this honour.

**The latest version**,** OpenSolver 2.1 **(6 Sept 2012) is available for download. Check out this post for the improvements made in the 1.8 version, here for the 1.9 fixes, and here for the 2.1 improvements.

**Welcome to OpenSolver**, the Open Source linear and integer optimizer for Microsoft Excel. OpenSolver is an Excel VBA add-in that extends Excel’s built-in Solver with a more powerful Linear Programming solver. It is developed and maintained by Andrew Mason and students at the Engineering Science department, University of Auckland, NZ. OpenSolver provides the following features:

- OpenSolver uses the excellent, Open Source, COIN-OR CBC optimization engine to quickly solve large Linear and Integer problems.
- Compatible with your existing Solver models, so there is no need to change your spreadsheets
- No artificial limits on the size of problem you can solve
- OpenSolver is free, open source software.
- Note that OpenSolver does not solve non-linear optimization problems, so your Solver model needs to have “Assume Linear Model” turned on.

As well as providing a replacement optimization engine, OpenSolver offers:

- A built-in model visualizer that highlights your model’s decision variables, objective and constraints directly on your spreadsheet
- A fast QuickSolve mode that makes it much faster to re-solve your model after making changes
- An algorithm to build and update the model only using information present on the sheet
- A modelling tool that we think improves on the built-in Solver window

OpenSolver has been developed for Excel 2003, Excel 2007 and Excel 2010 (including the 64bit version) running on Windows.

You can download OpenSolver.zip (which is hosted on our Open Solver Source Forge site). Version details (and dates of updates) are shown on the blog page.

OpenSolver is being developed by Andrew Mason in the Department of Engineering Science at the University of Auckland, and Iain Dunning. Kat Gilbert also made valuable contributions to the code while working as a summer student. Development of OpenSolver was made easier by the excellent Excel Name Manager which displays all the worksheet names used by Solver to store an optimization model.

OpenSolver is released as open source code under the GPL. This program is distributed in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose. OpenSolver uses the open source COIN-OR CBC optimization engine, the source code of which is available under the Common Public License (CPL). The CBC code has been written primarily by John J. Forrest, and is maintained by Ted Ralphs.

To build its optimization model, OpenSolver iterates through all the decision variables making small changes to each one while recording how the objective function cell and the constraint cells change. This allows the coefficients in the associated equations to be determined. Note that this assumes your model is linear; OpenSolver does some checks of this, but cannot catch every case.

**Citing OpenSolver: **Continued development ofOpenSolver is only possible if we can demonstrate its impact. If you are publishing work that uses OpenSolver, please cite both this *opensolver.org* website and this paper:

Mason, A.J., “OpenSolver – An Open Source Add-in to Solve Linear and Integer Progammes in Excel”,

Operations Research Proceedings 2011, eds. Klatte, Diethard, Lüthi, Hans-Jakob, Schmedders, Karl, Springer Berlin Heidelberg

pp 401-406, 2012, http://dx.doi.org/10.1007/978-3-642-29210-1_64, http://opensolver.org

@INCOLLECTION{OpenSolver,

author = {Mason, AndrewJ},

title = {OpenSolver – An Open Source Add-in to Solve Linear and Integer Progammes

in Excel},

booktitle = {Operations Research Proceedings 2011},

publisher = {Springer Berlin Heidelberg},

year = {2012},

editor = {Klatte, Diethard and Lathi, Hans-Jakob and Schmedders, Karl},

series = {Operations Research Proceedings},

pages = {401-406},

note = {http://opensolver.org},

doi = {10.1007/978-3-642-29210-1_64},

isbn = {978-3-642-29209-5},

language = {English},

url = {http://dx.doi.org/10.1007/978-3-642-29210-1_64}

}

The Excel Solver is a product developed by Frontline Systems for Microsoft. OpenSolver has no affiliation with, nor is recommend by, Microsoft or Frontline Systems. All trademark terms are the property of their respective owners.

Hi Andrew,

Thank you so much for your insights. I got you about working with binary variables on OpenSolver. Will try it out in this way.

Kindest regards

Thank Andrew for your prompt response,

Please elaborate on item 1 (making constraints cells that are binary and not part of the decision variables/changing cells). Below is an account of what I did and the details of the error messages I got:

I set up my binary variables under constraints by having the 1st row as the binary variables represented initially by zeros (no calculation or formulas in these cells). The second row contains the formula/calculations that link the uncalculated cells in the 1st row (representing binary variables such that some of the cells with initial zeros with change to 1 after solving the model) along with other variables elsewhere in the spreadsheet. This procedure works with basic solver which is limited to size of problem I am dealing with. Below is the error message I get when I add these binary variables (the cells with no formulas/calculations) as part of my constraints by specifying that say K509:P509 must be binary:

“Warning: an int/bin constraint has been set on cells that are not decision variables. OpenSolver does not support this yet.An error message will appear if you have selected to show the model”. Clicking ok twice the produces the error:

“A cell specified as bin or int could not be found in the decision variable cells.”

I then moved/copied my binary variable cells under constraints to the decision variables area, and specified that both the decision variables and the relocated cells should be binary and got another error saying “LHS of constraints must have only 1 area” instead of say “$k$221:$p$426, $k$421:$p$426″

The warning message above seems to suggest that opensolver does not support having binary variables if they are constraints and not part of the decisions variables (if I understand it correctly). Please advice on how I can resolve the issue.

If say B6 has a formula that must have be binary valued, use OpenSolver to add a decision variable cell, say A6, and then use OpenSolver to add two constraints: (1) A6 is binary, (2) A6=B6. Andrew

Hi, please help with the following 2 questions:

1. Does OpenSolver have provition to include binary variables that are not part of the decision variables/changing cells (e.g., constraints that are binary)?

2. After building a spreadsheet model (mathematically formulation) and using OpenSolver optimization engine to solve/implement the details of the math formulation, is there some kind of developed software/tool or a way to create an interface for users without having to learn programming? For example, creating something that allows user to input data such that this data communicates with both the spreadsheet model base (maths formulation) and OpenSolver behind the scene, then display the output or outputs back to the user.

Will really appreciate any assistance, particularly with regards to item 2 for someone with no programming knowledge or background.

To make a calculated cell integer or binary, add an integer or binary decision variable and a constraint to make it equal your calculated cell. I suggest you just build your interface on the model sheet with clearly indicated user input cells that feed values and options into the model that change constraint coefficients etc. Andrew

Hello, I need your help.

when trying to use OpenSolver 2.5.4 I get the following error:

Permission denied (at line 28860)

Source = OpenSolver, ErrNumber=70

do you happen to know what this is about?

thank you for your help!

cheers,

Yael

This is possibly because one of OpenSolver’s internal model files has been left open. Pl;ease try quitting Excel, and starting again. If this fails, look in your temp directory (which you can find using echo %temp% at the command line), and delete any recently created files (such as cbc.bat, model.lp, log1.tmp, any .nl files, etc). Also, can you tell us what you were doing, or if you had an OpenSolver errors, around the time this happened? What solver were you using? Thanks, Andrew

Looks like a very useful project, thanks. Where/how does one download the source code for OpenSolver?

Most of it is vba, so the code is in the spreadsheet. Andrew

Hi,

I sent a question about using SolverStudio instead of OpenSolver or along it.

Can you please comment? thx

Hi,

I am just making sure that my other post from yesterday arrived?

I can’t locate it, and it was quite long

I really need a feedback to know what to do…

thx!

Hi,

I used the program in the past and loved it!

Today I am working on a vast optimization model and as it looks like- opensolver will need hours to run it.

I read in other posts about the SolverStudio. I downloaded it and tried to work with it but unfortunately I found out that there isn’t a menu to build the model like in the OpenSolver.

The menu provided there (as far as I understood) can’t define all the parameters- for example: how to set the object cell- max a cell.

I don’t know programming languages, so setting a problem only by code is a real gap for me.

If I misunderstood something and there is a way to build the function easily I will appreciate if you can guide me!

Another thing-

I think that SolverStudio can provide additional ability to my model which OpenSolver can’t.

I have a dynamic range of variables (I download information from the web and according to the amount and type of data downloaded the pivot tables changes) so creating a fixed model causes it to be extremely big and not always necessary. I saw that SolverStudio can use the Offset function to limit the variables and constraints to fit the existing problem.

Will really appreciate any support! if I could send an example and then according to the answer I will keep building the model it will be great!

thanks!

You are correct that SolverStudio requires a model to be built in text, wbich requires some learning. Using AMPL is probably easiest, but AMPL is comercial and so you either need to buy it or run it in SolverStudio via NEOS (which works fine but does result in your model becoming publicly visible). PuLP is free… looking at the examples will hopefully show you it is not too hard. And yes SolverStudio works with dynamic named ranges defined using formulae. Hope this helps. Andrew

You may also wish to try the new experimental version which allows you to solve in the cloud using the NEOS CBC solver. This works differently in that it reads the sheet formulae, which can be faster. We would love to know if it works for you. Andrew

Hi Andrew, thanks a lot for your response!

1) If I choose to use your suggestion of using the cloud solver, will it be public/available to other users?

2) is there any chance I can send the help team an example file to model in code and I will repeat the process for all the rest of the file? the formation is the same (2 power pivot tables side by side with variable tables below them and a calculation table below these tables).

thx

The cloud solver NEOS keeps a record fo all submitted models. Your model gets converted into AMPL and submitted to NEOS, so yes, an AMPL representation of your model will be publicly available for people to FTP out of NEOS. If you want a model built, please contact Dr Stu Mitchell, our commercial support partner for SolverStudio. Andrew

Hi,

In OpenSolver-

Can I declare cell D1 to contain the phrase “A1:A10″ (which is my RHS) and D2 (which contain B1:B10, my LHS), so that when I use OpenSolver and enter my constraints I would type D1=D2 instead of A1:A10=B1:B10, and the software will recognize that I want to set the constraints on A1:A10 & B1:B10 and not D1&D2?

*like indirect function*

The same for in variables-

can I declare a name to the table I use for my variables (G1:g20) as “var_tbl”, and in the opensolver menu I will write var_tbl instead of G1:G20?

It is possible if I use SolverStudio in code but if it is also available here it will solve me the need to do it in SolverStudio.

You cannot use the ‘indirect’ approach but you can define named ranges and those names will show in the Model dialog (in the latest version). If you have a model that changes size, just add the maximum number of constraints you could ever need, and set it up so these are satisfied (eg 0 <= 0) when you have less data. Hope this helps. Andrew

Greetings,

I have a very large problem and even waited 7-8 hours to set up the problem. (I am running the model on 64-bit.)But then I noticed that when the solution screen came and i got the error that “cbc.exe has stopped working”. What is the reason for that?

Thanks in advance for your help.

Best,

Btw I got the OpenSolver Error Message saying:

“The CBC solver did not create a solution file. No new solution is available. (at line 12300) (at line 19450)

ErrNumber= -2147220503

A problem as big as yours would work much better in SolverStudio… 7 hours to set up is way too long! However, that aside, it looks like Cbc crashed. Please make sure you are using the very latest experimental OpenSolver release as that should run the 64 bit CBC as it checks the operating system bitness. Or you could be one of the first to try our new CBC on NEOS. This builds the model differently and so may be faster. Let us know how you get on. Andrew

Dear Andrew thanks for your quick response!

I’m going to keep your advices in mind and try CBC on NEOS and SolverStudio.

Then I am going to share my experiences.

Btw, I made some performance tests on CPU&Memory usage by shrinking the model size and noticed that CBC is most probably crashing due to the lack of memory. Because while setting up the problem, CPU is working a lot but as CBC starts memory usage climbs significantly. As I understand at that process, model is transferred to local and therefore memory faces with difficulty.

Is there any way to break that transferred into smaller pieces so that computer memory can handle?

Thanks again for your kind held.

Best,

OpenSolver writes the model as a .lp file. This file can be viewed using the Opensolver menu. As this file takes a long time to create, I suggest you make a copy of it. Then you can experiment with running CBC from the command line (again available from the Open Solver menu) and see whats going wrong. Andrew

Hye,

I am new in using this open solver. So, can this be used for ‘grg nonliner’ engine as in standard solver. How? Or does it can only be used for linear problems?

Hope to receive your prompt reply.

Regards,

Smurt

NOMAD and the NEOS-based solvers (apart from CBC) can be used for non-linear models. NOMAD will work on any spreadsheet, but with no guarantees on results. For the NEOS solvers (except CBC), we parse the spreadsheet formulae, and so OpenSolver maty not work if you have formulae in your model we do not (yet) support. Please try it out and tell us how you get on. Thanks, Andrew

Thanks for the response Andrew.

I’ve try using NOMAD, but there is run time error ’13′ window pop up. Do you have any suggestion?

Regards,

Smurt

Thanks for trying out our experimental release. Please confirm you are running the latest version (2.5.3), and what versions of Office and Windows you are using. Also, would you be able to email us your spreadsheet so we can test it on our system, to a dot mason at auckland ac nz. Thanks, Andrew

Thanks for letting us know about this. Please try the newest release 2.5.4 and see if that fixes the problem.

Thanks,

Jack

We think the latest release should have fixed this. Please let us know if we succeeded in getting the bug! Thanks, Andrew

Hi,

Opensolver has reported that it cannot find a solution and pointed to my objective function cell. I suppose it means the objective function cell is not built right for opensolver or cannot be handled by opensolver as it is. Please help me to resolve why this cell is presumably causing the error. I look forward to any assistance and will appreciate it.

What exactly is the error?

Hi Andrew,

Thank you so much for coming back to me. I do not know what exactly is the error (I am fairly new to use of opensolver). The program pointed me to one cell containing objective function after reportng that opensolver cannot find a solution. I can send my file for any help in checking what could be the error in the built model; will really appreciate that.

Thanks. However, we really need to know exactly what OpenSolver is saying. Please solve the problem again and explain in more detail what error OpenSolver reported (i.e. give us the OpenSolver error message text). Thanks. Andrew

Thanks Andrew. Below are the details of the error message:

“OpenSolver could not find an optimal solution, and reported:Infeasible

The model contains an Excel constraint “1.2INPUT DATA’!$B$414:$G$414=1′ for which instance 1 does not depend on the decision variables and is not satisfied.

Constraint specifies:LHS=B414=0=RHS=1=1

No solution was available to load into the spreadsheet.

CBC Solver reported: Unsolved”

On a separate but related note, I had to delete all constraints in relation to binary variable cells that I wanted to set as additional variable cells (decision variables) after getting the error “A cell specified as bin or int could not be found in the decision variable cells”. I wish to also find out if Opensolver supports this possibility of setting additional variables cells as binary?

I look forward to your valuable insights and assistance.

You have made a mistake in your model. Specifically, you have a constraint that says B414=1, but cell B414 (which is currently 0) does not depend on the decision cells, and so there is no way to satisfy your constraint. And, no, OpenSolver does not support specifying a non-decision cell as being integer or binary. You can, of course, add a new integer variable, and force it to equal the non-decision cell via a constraint. Cheers,

AndrewHi,I am a new user of opensolver and running excel 2007 (on a windows 8 laptop) and excel 2010 (on a windows 7 work PC) and wish to ask for help about the model I have built. The model has a really large number of variables (some with 2 subscripts, others 3).I wish to also find out if open solver can handle this kind of model from the experts (after my reading informed me that it can handle more complex problems than solver). I am confident about most equations but one (involving variable with 3 subscripts). Please help me to check if the model I have built is right for and can be handled by open solver. Also interested to check how much time it will take open solver to produce an optimal solution. I can send an e-mail with my spreadsheet model and would really appreciate any assistance to get the model right for open solver.

Hi,

I have win8 and MS Office 2013 installed.

I downloaded the latest version and tried to solve a model I built- got this error:

“Open Solver encountered an error:

The CBC solver did not create a solution file. No new solution is available. (at line 12300) (at line 19540)

Source= opensolver Model Solving, ErrNumber= -2147220503″

What shall I do??

CBC has crashed; see here for more information. Good luck, Andrew

Hi andrew,

my question is, has the Excel SOLVER than 32 changing cells, few Open Solver changing cells have?, Or Open Solver only increases the number of variables and constraints but not varables decision?

Andrew,

thanks a lot for the great piece of software. I find opensolver extremely extremely powerful

I have a very quick question about integration with CPLEX as a solver (instead of CBC). Does anybody try this? I assume the .lp generated as input for CBC is fully compatible and can be loaded directly to CPLEX. But, is there a smart way to parse .sol (CPLEX output)?

I would love to hear any experience you guys have had with similar gimmicks. Due to some constraints (with other people understanding the model) I can’t directly upgrade to more advanced solutions (like Solver Studio) while CPLEX Excel plug-in seems to be useless.

thanks for any hints

Hello. Why is SolverStudio 6.0 faster than OpenSolver? My understanding is that OpenSolver uses CBC written in C++. Where does the speed difference come from?

An OpenSolver spreadsheet with 1000 variables takes 1000 spreadsheet recalculations to extract the model equations ready for CBC to then start solving. These 1000 recalculations can be slow. SolverStudio stores the model equations directly (using user input via a modelling language), and so is often faster. Hope this helps, Andrew

Hi,

How do I install opensolver on Windows 8? I get a command (black) screen with Coin: when I click on the application, after downloading and extracting all the files to my Program files location. Please help?

See http://opensolver.org/installing-opensolver/

I have problem with usage DEA (in Add-Ins menu)in following message:

There are more than 20 DMUs,

Please get the full version of the software.

please advise me. thanks.

Respected sir,

I have four inputs and two outputs ,out of four inputs one of them is text term that has been varied ie chemical name 1 mpms,aps,mtmos these are varied along with other parameters and out put is been effected by all input parameters.This is done by taguchi l9 array.can i use this sexcel solver to solve my problem

Hi Andrew,

I have 2 questions. 1) Is it possible to see the comment that appears on the screen right before you see the result again?

2) In my model all my decision variables were set as binary type . then I added a number of decision variables as integer type to the previous model. and after I ran the new model the result have decimal points such as 1.25,… for both binary and integer type variables!

Can Opensolver disregard its constraints? I wanted binary and integer output but it just decided to give me decimal ones?

My maximization objective function has grown 10 times as a result of making binary variables decimal.

It is Zoha again with another question. My model use to take 90 minutes to run. now more or less the same model takes forever and I have to just cancel it AFTER 15-20 hours. not sure what i have done to it but it might be an option I changed or checked. Could you tell me about the option ( Maximum solution time seconds). Should it be 99999999 as the default value. Right now what I have in is 99999999 which will take over 3 years! should I reduce the time to say 999secs? also I checked the ” show optimization progress while solving” hence I see that when the cbc window keeps running after a few hours it has already reached the optimal solution or the solution I use to get from it but it just keeps running and adds lines to the cbc window and the next line I see on CBC seem to have the same optimal solution. I should add that my tolerance is at 0%. however i have tested it running only slightly longer with the 0% tolerance in.

Thank you once again for helping me out.

Hard problems take a long time to solve to optimality and then prove this (which is what you see CBC doing, but remember it may find a better solution if you keep waiting). You can limit the run time directly or by increasing the tolerance; you have to decide what quality of solution you are prepared to tolerate. Andrew

Hi Andrew,

Once again thank you for your helpful comments

I am successfully running my model with over 700 variables and a few thousand constraints now. It takes 90 minutes to run. My logical constraints seems to have been met and the result looks alright except for one this:

when I change the right hand side of my labor constraint say from 400 to 1000 the value of my maximizing objective function drops a little bit from 23,900 to 23,800. in the case of 400 the labor constraint left hand side stops at 346 and when I set the limit to 1000 it stops at 342 so they are both non binding too…. Do you think there is an explanation there. are there settings that can differ on various runs of the model. Not sure where is the best place to look for error

I should add I ran versions where labor was binding I started from 50, then 100, then 150 ,….. and each time the objective function improved only when I became non binding instead of staying the same it started dropping. Let me know if I haven’t explained well. Thank you very much.

I would suggest you reduce the tolerance to 0% from the default 5%. Andrew

Hi Andrew,

I asked you some days ago about an eventual binary variables limit. I want to specify That i had integer solution With LP so not really “hard” but infeasable with IP with an error msg “cbc stopped working”

Thanks

I’ll like to thank you for this free software that is quite useful. I’ll be write a blog post about how to install and use Opensolver on my website.

Hello Andrew,

I am stuck please help me…..I have problems using OpenSolver within VBA code.

I have constructed the problem in Excel sheets using VBA. My objective function is in one sheet and each set of constraints are in a separate sheet. like 7 sheets in total.

I have copies and pasted here part of my code that uses excel solver. what I was doing or what I thought I was doing is using regular excel solver with VBA code to set my objective function and variables and then wanted to press solver in OpenSolver to solve it. In my first version I had the whole problem constructed on one excel sheet( objective function, variables and constraints) however, Now that my constraints are in different sheets it does not work and when I solved it only solved for the set of variables that are on the last sheet and drops the constraints from other sheets….. can you help?

I was not able to find a simple vba code for Opesolver to not have to use the codes for normal solver as below. Is there anywhere an example, like an optimization problem done in Opensolver so I could look at and read its code and learn how we can use open solver

My part of code :

Sub MYSolver()

Dim obj As Worksheet

Set obj = Sheets(“ObjFunction”)

Worksheets(“objFunction”).Activate

SolverReset

SolverOptions precision:=0.001

SolverOK setCell:=ActiveSheet.Range(“K2″), _

maxMinVal:=1, _

byChange:=Range(“$J$2:$j$5905″)

SolverAdd cellRef:=ActiveSheet.Range(“$J$2:$J$5905″), _

relation:=5, formulaText:=”binary”

‘Const1

Dim exe3 As Worksheet

Set exe3 = Sheets(“Macro1″)

Worksheets(“Macro1″).Activate

For o = 0 To 31

SolverAdd cellRef:=ActiveSheet.Cells(373, 4 + o), _

relation:=1, _

formulaText:=21

o = o + 1

Next o

‘Const2

Dim exe4 As Worksheet

Set exe4 = Sheets(“Macro2″)

Sheets(“Macro2″).Activate

lastcol = exe4.Cells(4, Columns.Count).End(xlToLeft).Column

For K = 4 To lastcol – 1 Step 16

For j = K To 11 + K Step 2

lastrow1 = exe4.Cells(Rows.Count, j).End(xlUp).Row

lastrow2 = exe4.Cells(Rows.Count, j + 2).End(xlUp).Row

exe4.Cells(lastrow1, j).Select

SolverAdd cellRef:=ActiveSheet.Cells(lastrow1 + 1, j), _

relation:=3, _

formulaText:=0

Next j

Next K

Thank you !

I assume this is because the Solver VBA interface does not allow you to add constraints from another sheet. (Solver does not allow such constraints, so the Solver VBA interface may block them.) Are you able to test this? If this is the case, I suggest you build the model manually using OpenSolver, and then just set the data using VBA; is that possible for your application?

Thank you very much for your help. I truly appreciate your help. If I use the OpenSolver Manually ( Use the form on the data >> OpenSolver tab) and specify objective function on one sheet and each constraint on a different sheet, it works!! However Im talking about over 2000 constraints at least so doing it manually is not possible I just tried a couple of them to check and see if it works and it did. To specify my question again what I understood was normal excel Solver does not understand a problem that is spread on different sheets but OpenSolver does! How can I use OpenSolver’s commands in VBA to make it read constraints from different sheets and understand they are all part of the same problem just like what happens when I use the OpenSolver form in the data>> Opensolver tab. The confusion for me comes from the fact that I can use excel solver’s commands and run OpenSolver at the end. If the commands are the same how is it that one understands a problem spread on different sheets and one does not. Should I be using a different command for OpenSolver?

To summarise your question, you want to use VBA to add constraints to a model where the constraints are on different sheets. Your experiements suggest you cannot do this using the Solver VBA interface. OpenSolver does not provide any VBA interface for doing this either. However, you can do it manually, but it is not straight forward, as you need to work with low level data. This involves adding named ranges to the spreadsheet, such as “solver_lhs1″ and “solver_rhs1″, whose values are references to ranges on the spreadsheet that contain the parts of the constraint. Also, “solver_rel1″ etc give you the < =, =, or >= relation for a constraint (and also the bin/int options). You also need to set solver_num to be the number of constraints you have in. I suggest you download and use it to see how a model is stored internally, and use this to help write the VBA you need to add extra constraints to an existing model. Sorry we cannot be of more help, but rest assured that adding new functionality to support this is now on our TODO list. Cheers, Andrew

Thanks for providing OpenSolver, but may I ask for some help in installing it.

I downloaded the OpenSolver, extracted the files and double clicked on OpenSolver.xlam. All fine: The commands appeared and I could run an LP_model open in my Excel. But then I wanted to install OpenSolver permanently, have tried different storing locations for the files,last this one: C:\Program Files (x86)\Microsoft Office\Office14\Library\SOLVER. But nothing happens. What am I doing wrong?

Problem resolved!

Pleased that it is working. Andrew

Thank your for this response – and for the OpenSolver in general; great program. But I still have a question (see also my mail under Help: Have a large LP where I have to try out different values for just one coefficient in the objective function; everything else the same. Can I use Quick Solve? If not can I somehow escape having to wait 10 min. for the setup every time?

No, QuickSolve does not handle changes in the objective function coefficients. However, the next version of OpenSolver will give cost sensitivity analysis, which may help you. Andrew

Thanks, look forward to the next version!

Unfortunately, no. This is not for any good reason, but simply because we have not needed this functionality. Note that the next release of OpenSolver will give cost sensitivity analysis, which may help you. Andrew

Hi Andrew,

I am trying to use OpenSolver fora linear assignment problem and I was solving for a problem with about 6000 binary variables. Even before putting in any of the constraints, it took the solver 12 minutes to come up with the result.

My objective function was a profit maximization so all it had to do was to come up with zero for negative coefficient and 1 for positive coefficient.

Should I expect like 10 hours of running if I put the constraints in? Do you think the run time was normal for 6000 variables or am I doing something wrong here ?

If I switch to solver studio would it be faster?

Thank you

6000 variables means doing 6000 spreadsheet recalculations. This can be slow on big spreadsheets. SolverStudio skips this slow step, and so should be faster. Andrew

Just wanted to say AWESOME! Well done Andrew and also those who provided bits for you to use.

Auckland University gave the world “R” and now we have this. Go kiwis!

Matt

Guangzhou, China.

Will open solver work with If-Statements? If not is there a known work around?

If statements are no linear, and so do not work in OpenSolver. You need to use binary variables; see a book on optimisation modelling for more details. Andrew

Hey,

Any ideas how to formulate/define soft constraints vs. hard constraints with OpenSolver?

Add slack/surplus variables, and penalise them in the objective function. (See any standard linear programming book for more details.) Andrew

There’s an option on the bottom of the Model for:

“Shadow Prices: [check] List constraints and shadow prices in a table with top-left cell: [cell]”

What is this used for? Is there a way to list constraints in spreadsheet form versus filling them into the Constraints table? I’m looking for a way to organize and track constraints a bit better.

Thanks!

I’d suggest using SolverStudio if you really want to make your constraints organised. Otherwise, try Googling Shadow Prices to find out about this feature. Cheers, Andrew. PS: Viewing the .lp file will help you check your constraints.

I have a question about opensolver, I just download it but it doesn’t work. When I press Solve it says that it could not find the file

The ErrNumber = -2147220501

Any suggestions for an open source non-linear solver?

COIN-OR has some good ones, but they don’t (yet) integrate with OpenSolver! Andrew

Hi,

I really like your tool! Thanks a lot for sharing it!

I have 2 questions-

1) After I work with the tool and close the file, when I reopen it the OpenSolver doesn’t appear… Any ideas how to solve that? (I saw that this Q has been asked already)

2) I want to create a discount for # of products (quantity) and for total purchase amount. I wish that my problem will check if it is better to buy more products from someone (or in more money) to gain a better discount rate, and if so will change the result accordingly. I have no idea how can I do that without using the If function (but this is NonLinear) and really appreciate any assistance!

Thanks!

1) See http://opensolver.org/installing-opensolver/

2) This sounds like a homework problem?

Hi,

No, that’s from real life situation

Say I have 5 stores that can sell me 15 products (identical). Each gives me a price list. For every item. I can also create 1 product from 2 stores instead of buying 100% of the item from a specific store. Therefore I have another price list to do the mixes (meaning prices for 50% of each product).

Now, to do the optimization is easy, but when they said that if I buy mors between 5to 9 products I will get x% reduction, and if I buy 10-12 y% discount and 13 z%… each discount % is different between suppliers and the price list (i.e for 100% product and for 50%)…

I tried different approaches but couldn’t find a solution… I wonder if you have a suggestion, since you are the expert

Thanks!

An easy (tedious) apporach is to create more products, so you can buy 1 of “5 products with an x% reduction”, or 1 of “6 products with an x% reduction”, etc. Andrew

Thanks for your response! I am missing something- if I create discount products in addition to the price list I have, it will choose them for sure… but how will the tool know that it needs to pick 5 products to be able to use that producr price?

Hi Andrew,

Sorry for asking that again, but I was hoping you can explain what you said…

If I get different discount rates based on the amount I purchasing from the store than I have a nonlinear model (as far as I know)…

What you said if I understood correctly is to add a fictive price list with prices after discount, but then the optimizer will choose them (since he can’t tell that he need to buy in a certain amount of the discount to take place and if it is better to buy even more to get increased discount).

Combining/tying the discount rates with the amounts to buy from each supplier is what I can’t understand how to build using OpenSolver.

Thanks!

You can manually enumerate all the purchase options, and use binary variables that determine which ones you select. An option is purchasing some predetermined quantity at some total price. Andrew

Downloaded OpenSolver2.1 on a Windows with Excel 2007 and keep getting VBA error message “Compile error: Can’t find project or library” when trying to run OpenSolver. In the VBA, the error message is occurring at line 8170 where “NumConstraints = Val(Mid(Name….. ” and the “Mid” is selected.

Any ideas for how to resolve this or what I may have done incorrectly to arrive at this?

Thanks in advance for any help!

Geoff

A VBA error message “Compile error: Can’t find project or library” normally indicates a problem with RefEdit in your Excel installation. (Try adding a RefEdit control to a VBA form to test this.) Using Excel’s “Repair installation” should fix this. Hope this helps, Andrew

Hi,

I am facing an issue in Solver using VBA.

I have put my constraints…below is the code

SolverReset

SolverOk SetCell:=”$X$39″, MaxMinVal:=1, ValueOf:=0, ByChange:=”$V$16:$V$37″, _

Engine:=1, EngineDesc:=”GRG Nonlinear”

SolverAdd CellRef:=”$V$16:$V$37″, Relation:=1, FormulaText:=”$U$16:$U$37″

SolverAdd CellRef:=”$V$16:$V$37″, Relation:=3, FormulaText:=”$T$16:$T$37″

SolverAdd CellRef:=”$V$39″, Relation:=2, FormulaText:=”$D$8″

SolverOk SetCell:=”$X$39″, MaxMinVal:=1, ValueOf:=0, ByChange:=”$V$16:$V$37″, _

Engine:=1, EngineDesc:=”GRG Nonlinear”

SolverSolve userfinish:=True

When i execute it line by line (using F8) its working fine…but when i try calling the procedure…its not working

Thanks in advance

Thanks!

Rajvarman

This sounds like a general VBA (non-OpenSolver) problem, perhaps caused by you not adding a reference to a library?

Hi Andrew

Following is a very brief summary of the approach I am using to solve large non-linear problems with OpenSolver. I can furnish more details (real examples etc)if required later.

A linear relationship between variables X, Y, Z is of the form

Z = AX + BY + C, where A, B and C are constants. An optimisation problem with any number of such linear relationships is solvable in one go using LP. An initial condition is not required.

If a non-linear relationship exists of the form Z = F (X,Y,…) where Z is convex in the valid range of X and Y with an initial condition Z0 = F(X0,Y0), then A0 = dF/dX at X0, Y0 (partial derivative) and B0 = dF/dY at X0, Y0 and C0 = Z0 – A0X0 – B0Y0

Using the linear relationship Z = A0X + B0Y + C0, solve the problem with standard LP. The solution will provide Z1(old), X1, Y1 based on this relationship. Disregard Z1(old) and find a Z1(new) = F (X1,Y1) and also A1, B1, C1 using above. Also, calculate the error (Z1(old)-Z1(new))^2

Repeat this process till the error converges to an acceptable value. If the problem diverges (ie error increases or is erratic) the relationships are not convex (or maybe too convex) and the problem cannot be solved using this iterative LP approach.

Has worked always for me in the area I am involved in.

Hello Admin,

How many variables can my model contain. I am looking at an assignment problem that could have a couple of million variables.

Can I still use Open Solver? If not what is the recommendation?

Thank you

No limit. Try it, and see if it works ok. If not use SolverStudio. Andrew

I have just downloaded opensolver on a new computer with excel 2013. If I run the xlam file that came with opensolver, the opensolver add-in is available to use on my spreadsheets. If I close excel and then open it again (without running the xlam file), opensolver is not available in excel. Is there a way I can make opensolver permanently available in excel every time I open excel? Please email me the response, thanks.

0. Its covered in the ReadMe in the download ZIP that i pulled today.

1. First go to File > Options > Add-ins and note where you current plugins are found.

2. Copy the OpenSolver folder in the download to the add-ons folder.

3. Now, still in the Add-ins screen look at the bottom where it says “Manage” and make sure Excel Plug Ins is selected. Then click on Go.

4. You can Browse to the location of your OpenSolver folder and click on select folder.

5. Click on the checkbox next to OpenSolver when it appears in Add-Ins dialog and then click OK.

Thank you very much Andrew.

I am not familiar with SolverStudio but if that one can handle 2 Million variables why not use that in the first place?

I mean which is better SolverStudio or OpenSolver ? Thank you

SolverStudio is “better” for advanced users as it will generally run faster. Andrew

Thank you very much Andrew.

I am not familiar with SolverStudio but if that one can handle 2 Million variables why not use that in the first place?

I mean which is better SolverStudio or OpenSolver ? Thank you

Hi Andrew

Thanks for the reply and giving the opportunity to share what I have. I have nothing written so far but will have a document ready early in the new year. I will let you know when it’s ready.

Hi Andrew

Thanks again, look forward to getting this new version which blocks updating of dependent data on other sheets during the optimization.

I have been consistently using OpenSolver for the last two years and thought it would be prudent to let you know of the type of applications I use it for (for whatever it’s worth)

I work for Snowyhydro Ltd who is the owner/operator of the largest hydro power complex in Australia (the Snowy Mountains Scheme). One of my responsibilities to develop optimization tools for the dispatch of our hydro generators in the National Electricity Market. As you know, hydro scheduling is essentially a complex non-linear optimization problem, with turbine efficiency, electrical loss, reservoir data etc, all being represented by non-linear functions.

With the help of OpenSolver, I have been able to develop an approach to solve large scale non-linear models using an iterative technique. The only requirement is that the non-linear functions be convex (ie the second derivative is always positive or negative in the range you are looking at)

If it’s of any value, I can send details of this approach, particularly for the benefit of other users of OpenSolver.

(I also extensively use the Excel non-linear solver which is great for problems with only a few decision variables)

Thanks for letting us know what you’re doing with OpenSolver. Yes, we’d love to know more… either via a post, or if you have anything written up then I’d welcome the chance to put it up on the OpenSolver site. Cheers, Andrew

Thanks for sharing your results about being able to solve nonlinear convex functions iteratively. Can you please share your procedure and results. In a test application, I was seeing that this was not happening as the results seem to get reinitialized.

Thanks, Raj

Hi Andrew

Thanks for the reply. My workbook is quite small (only 85 kB at this stage of the development)so I don’t think its the size that’s giving the problem. However, there is a graph of 48 time-series values being updated with the optimisation. What I found today is that if I remove this graph, the problem goes away. Maybe its the recalculation time of the graph that’s causing it?

Anyway, I have now disabled the linearity check in the OpenSolver options and the problem doesn’t come even with the graph. All’s well that ends well.

I’m pleased you fixed it. However it is a puzzle as the linearity check is not very different to the other calculations done by OpenSolver. By the way, I am trialling a new version of OpenSolver that allows you to prevent Excel updating dependent data such as charts (assuming they are on other sheets); this can give big time savings in some cases. Cheers, Andrew

Hi Andrew

Sorry I posted the comment by accident before it was finished.

Opensolver has been great and is helping me a lot at work. However, I get this error message in my latest model. The message is intermittent – ie sometimes comes and sometimes doesn’t – I am pretty sure the problem is linear as it is solved sometimes.

OpenSolver: Calculation Error Occured

Warning: The worksheet calculation did not complete during the linearity test, and so the test may not be correct. Would you like to retry?

However much I click on retry, the message comes back. Can you help?

This is something we don’t really understand, but sometimes Excel reports the spreadsheet as not having finished re-calculating. We have seen it happen on very large spreadsheets. Is yours a very large workbook? If so, I suggest you make the workbook smaller (eg by deleting any sheets that depend on the decision cells), or switch to SolverStudio. Note that if this happens in the linearity check, it will also happen in the actual model calculations that come next. Sorry I cannot be of more help. Andrew

Hello, is it possible to use Open-Solver in a heuristic way by setting a certain runtime and cancle Branch&Bound? How can I get the until then best found solution?

Hi,

A very quick question. what is the commend in VBA to start the open solver.

Example, starting the regular solver is just SolverSolve.

Thank you.

See the Using OpenSolver page. Andrew

Hi;

I want to know that what is the differences between “opensolver” and ” excel solver” solution. I tested my model in both program and got the same solution. Then, what can be said if we compare these program?

Thanks.

For linear models, they should both give the same answers. Solver can also solve non-linear problems, which OpenSolver cannot.

Hi Andrew;

Thank you very much for your support. I have one more question. What algortihm does open solver use to solve LP (Lineer programming) and IP (integer programming). Simplex, genetichs algortihm, or what?

Also, is it use the same method with “excel solver programme” to solve these kind of problems?

OpenSolver uses the COIN-OR CBC solver which uses the Simplex algorithm and branch and bound. This only solves linear programmes. Solver also uses similar algorithms for linear models. Andrew

Hi Andrew;

Thank you for your support. I have another question. Does open solver use simpex method or genethics algorithm to solve Lp (Linear programming) and IP(Integer programming)problems. OR which method does it use?

And how about excel solver? is it use the same method with open solver to solve these kind of problems ?

Thank a lot. I have 2071 constarints and 2025 variables. it solves easily. I wonder how power is this programme? I mean how many constraints and variables can be worked by this programme ?

There is no limit other than your memory and time. Andrew

Hi Andrew

I really like opensolver. And the quicksolve option is much faster than normal solve for my problem. However, quicksolve does not always produce the optimal solution for my LP. That is, sometimes, when I run a normal solve after quicksolve the objective improves!

My mistake. I was missing some of my RHSparameters in quicksolve-setting. Since it was only a few the solution was optimum or pretty close to in most cases.

Thanks for an nice solver add-in for excel.

Hi,

I will like to know two things?

1. What procedures do I call with VBA to perform an LP Eg. I will do the following in MS Excel

SolverOk SetCell:=”$Z$21″, MaxMinVal:=1, ValueOf:=0, ByChange:=”$Z$2:$AN$16″, Engine:=2, EngineDesc:=”Simplex LP”

SolverAdd CellRef:=”$Z$18:$AN$18″, Relation:=2, FormulaText:=”$Z$19:$AN$19″

SolverAdd CellRef:=”$AP$2:$AP$16″, Relation:=2, FormulaText:=”$AQ$2:$AQ$16″

SolverAdd CellRef:=”$Z$2:$AN$16″, Relation:=3, FormulaText:=”0″

2. What is the maximum range of variables one can use in Opensolver? Can it handle say a 200×200 variable

transportation problem?

Thank you

See the Using OpenSolver page on how to run OpenSolver from VBA. I suggest you set up the model outside the VBA, but your code above will work fine with OpenSolver if you prefer that approach. There is no size limit. Your transportation problem should be easy to solve. Hope this is useful. Andrew

I am writing the code for someone who actually only wants it in Excel so I guess I have no choice. Thank you for the response Andrew.

I was running Solver Studio on NEOS server for one day using AMPL. But today, when I turned on my computer and opened my excel file to continue experiments (MS Excel 2010), the Solver studio does not appear on my toolbar in “Data”. I tried to reinstall acouple time, but it did not work. I would really appreciate for help!

Have you uninstalled SolverStudio using the Control Panel? Please post any replies on the SolverStudio, site. Andrew

Yes, I used the control panel. The Problem was with Excel. If I stop Excel from task Manager then OpenSolver add-on becomes disabled, and you have to enable it in the Excel “Options”.

P.S. Sorry could not see the “Leave the reply” on the http://solverstudio.org/ Is it enabled?

Dear Andrew,

We had over 50000 downloads of open solver and hence are a big community now.

The last stable version of Opensolver is now over 1 year old.

We have heard news of integration of NOMAD non-linear solver to the open solver platform in Feb,2013.

We are looking forward eagerly to see what new additions have been done to open solver.

Request to kindly set the next release up as soon as possible.

Many Thanks,

Parag Jain

Hello,

I would like to ask. What is the difference in the process when I just press “Solve”-button and press “Solve LP relaxation”? The running time is much faster when I press “Solve LP relaxation”.

The latter allows solutions to have fractional values like 0.67 even if you have bin or int constraints. Andrew

This is extremely awesome and helpful. Thx for all who participate in this initiative.

This is simply amazing.

Keep calm and trust open source!

Warmest regards,

B.

Hello,

I am new to this excel problem solving world and trying to find the right tool for me. I have read about OpenSolver and a few other products such as What’sBest by Lindo and Excel Solver by FrontLine Systems. Can you please help me understand the differences between all three and recommend any other products if they are out there.

Thank you.

Firstly, welcome to the the world of optimisation. I haven’t used Lindo’s What’s Best, so cannot comment on this. I teach with Frontline’s Solver. Unlike OpenSolver, Solver can handle non-linear models, and also has a very clever upgraded version that uses their Psi engine to analyse the spreadsheet formulae, giving faster solve times for bigger models. They also offer simulation/risk capabilities, which look nice. IBM CPLEX also have an CPLEX Excel add-in. Hope this helps. Andrew

Hello, I have a question about solving a large set partitioning problem with OpenSolver.I just solved a SPP with 32751 Variables and it worked in around 20 minutes I guess. Since OPenSolver is some kind of “Black Box” I would like to know which algorithm it is using while solving the SPP. Is it branch and bound or how is it working exactly?

Thanks

I need to put two ranges in the space provided for decision variables. However, OpenSolver is picking only one range at a time. Is there any easy way out?

Hold down the Control key when selecting the second range (or just type them in separated by a comma).

Thanks.

I have been trying to collect statistics such as the time taken to solve the problem, the number of variables, constraints etc, but still haven’t found a way to do that. Hope that you can help me out. Thanks!

Firstly, solve the problem once. Then, if you view the .lp file (using “View Last Model .lp file”), you will see how long OpenSolver took to create the LP model (and also its size), as in:

/ It took 0.7460938 seconds to build the model.

You can then open this model in CBC (using “Open last model in CBC”), and solve it again by typing “solve”. CBC will then give you the solution time, as in:/ Model has 2 Excel constraints giving 8 constraint rows and 15 variables.

Optimal objective 819 – 7 iterations time 0.002Alternatively, you can write some VBA that does all this timing for you! Hope this is useful, Andrew.

I define the variables range as Sheet1!$H$3:$R$14, Sheet2!$H$3:$R$14 and I get an error when saving the model. I look at the code in the variable s I have

=Sheet1!$H$3:$R$14, Sheet2!$H$3:$R$14

Notice the “=” sign at the beginning!

How do I correct this?

Hi Andrew,

I have created model using Opensolver. I want to execute this model using Macro on a button click.

Can you please help me out how to do the same.

Regards,

Rahul

Calling OpenSolver from VBA is documented on the OpenSolver.org site under Using OpenSolver. Hope this helps. Andrew

Every time I open Excel 2007 – openSolver initialises the last model.

How do I disable that behaviour?

Thanks

What do you mean by “initialise”? OpenSolver does not run on startup, and so should not change the model. Andrew

Hi Andrew, I am back after some time. OpenSolver works fine, but what is SolverStudio? Is it supported by you? Is it a free download? What advantages does it have over OpenSolver? In particular, can it help to reduce the model set up time?

Welcome back! SolverStudio is supported by us, and is a free download. It reduces the model build time significantly, but at the expense of you having to develop the model using a formal modelling language such as AMPL, GMPL or PuLP. We’d recommend using a modelling language anyway for big models; SolverStudio let’s you do this but still stya with Excel. SolverStudio also lets you solve models in the cloud with free access to linear and non-linear solvers. We’d welcome your feedback if you give it a try. Cheers, Andrew

Any plans to incorporate BONMIN (nonlinear mixed integer) to OpenSolver? The 200 limitation on Excel is appalling.

We have an unreleased beta running NOMAD. Bonmin will be more work, but is something we are thinking about. Andrew

Je vous remercie pour cette aplication, elle est tellement utile pour mon probleme, un grand merci pour vous, bonne continuation.

Can anyone help me in solving an optimization problem which has got 27 variables. Please reply to my mail ID email hidden; JavaScript is required. I will send across the data.

GREAT JOB!

Thank you…

I have a MILP with approximately 2000 variables and 2500 constraints. It solves it in seconds with my 3 year old laptop

Thank you again…

You should thank the CBC folks for writing such good optimisation software…

PERFECT!!!!

PERFECT!!!!

PERFECT!!!!

1000 times faster than standart solver. Thank you for your great job. God bless you!!!!!!!!!!!!!!!!!

I would be surprised if that is the case… maybe you were not telling Solver that the model was linear? OpenSolver just assumes this (because it cannot handle non-linear models).

We are running a large LP flood control model using your solver and it takes many hours to complete a run. I’ve considered upgrading my 4GB computer to a new computer with more memory (8-24 GB). Would this help? Your run-time text says something like “writing model to disk.” Does this occur when the RAM capacity is used up (similar to the Windows page file) or is solver programmed to always write the model to the hard drive before solving it? Thanks, Nathan

Flood control; what a great application for optimization. Big models can be slow with OpenSolver. OpenSolver firstly analyses the model by changing each decision variable and re-calculating the spreadsheet each time; this is normally the slowest step. You can watch OpenSolver count up thru the decision variables in Excel’s ‘Ready’ status display as it does this step. It then writes the resulting model out to a text file (which it does regardless of the amount of memory you have); this should not be too slow as long as you are not working over a network. (It writes to something like c:users[logon name]AppDataLocalTempmodel.lp.) This file is then given to the CBC optimizer, which produces the result that OpenSolver loads back into the sheet. If you want it all to run much faster, I suggest you try the free SolverStudio that we also develop and support; see http://solverstudio.org This is aimed at users with bigger models, and will typically be much faster. We can help with the translation to SolverStudio if you wish. Cheers, Andrew

OpenSolver very useful. I am running a VBA problem using OpenSolver, running a few times to get around a non-linear problem .. is there a way to block the CBC ‘infeasible solution’ message?

If you are using a version of the VBA code at http://opensolver.org/using-opensolver#vba, then OpenSolver should give an error, not display a dialogue, if the problem is infeasible. If this is not happening, then I’d like to know so we can fix it in the next release. Thanks, Andrew

Before I start with my questions (I have a couple) I want to thank everyone on the development team for such a great product. Easy to install and use, free and top-notch.

Best regards,

Mario

Hi,

I have a 50,000 variable and 5 constraints “supposedly” linear integer programming problem. However, some of the constraints involve ‘vlookup’ and ‘countif’ statements which might make the problem non-linear. Opensolver works beautifully on the problem and also provides me with a consistent solution every time I run the problem, but just before providing the solution, it generates a warning saying “Problem might be non-linear”. I am confused about the mixed message – the warning as well as the solution to a so-called non-linear problem because I thought Opensolver only solved linear problems.

Any inputs?

Thanks!

OpenSolver assumes the problem is linear. vlookup and countif functions typically mean this is not the case, but OpenSolver cannot determine this during its initial testing of the problem. However, if OpenSolver detects at the end of the solve that the values it expects to see on the sheet (assuming a linear model) are not the values it gets when it puts the ‘optimal’ solution onto the sheet, then it produces the warning you are seeing. I suggest you check out a book on integer programmings, and find another way to set up your problem that avoids the vlookup and the countif. Or, you can buy one of the advanced Solver products from frontLine which will do this automatically for you (I gather; I have not used these). They also provide algorithms for non-linear problems, but these typcically cannot guarantee the best solution has been found. Hope this helps, Andrew

I have used opensolver and I find it very useful. Now, I have another problem that has some “if” statements in my constraints that depends on decision variables and so oepnsolver complains that the problem has become non linear. Can you suggest a solution? If there a work around to continue to use open solver for such problems? Is there an alternate tool?

You need to reformulate the models using 0/1 binary variables. I suggest you look at an optimisation modelling textbook to see how this is done. Cheers, Andrew

I am trying to link external processes to the excel solver. The external code uses a data file as input and creates a data file as output.

Managed to wrap this process into an excel function that works as a one off function, but, the Excel solver, it produces errors.

Can opensolver wrap external processes into a optimazation problem?… still reading commments on the website.

Thanks for any tips or guidance.

mark

I would suggest you use one of the COIN-OR solvers directly from your code – there is no need to use Excel if you just want to solve an optimisation problem. See http://coin-or.org. THe http://neos-server.org optimisation guide might be useful as well. See also http://SolverStudio.org as another way of creating an integrated solution using, for example, Python, but still based around Excel. Hope this helps, Andrew

Hi, thank you so much for your awesome program. its helping me so much with my project.

I’m however facing a difficulty when i am trying to add a constraint A1<A2 and A1 has formula: =SUMPRODUCT(–(AS$4:AS$1039),–(ISNUMBER(SEARCH("Mixing",$D$4:$D$1039))))

I get the error notification: "the formula or value on the RHS is not valid, please try again"

i learnt that Solver does not work with Sumif, which was why i converted to sumproduct but still its having problem.Can you please help?

and thanks again for the great work!

Thanks for your feedback. Solver works fine with sumif, as long as the “if” criteria do not depend on the decision variables. I suspect your formula is either non-linear, or giving an error of some type. Sorry I cannot be of more help. Andrew. PS: If you wish to send me your sheet, then I will use it as part of my task to “add better error checking for invalid RHS”!

Hi Andrew,

thanks, i will send you a copy of my spreadsheet for your review.

I have another question. whenever i copy a sheet, the model doesnt seem to work, the error message is “A model was found on the sheet but the decision variable cells () could not be interpreted. Please redefine the decision variable and try again.” How do i solve this? I copy the sheet so that I can create different sheets for different variables.

Hi,

Thanks for this excelent tool. I have a question, I have a model, I need to change a parameter in case the model would be infeasible. What is the best way to do this throught a Visual Basic code?. I mean I need something like…

run model

if model is infeasible

change a paremeter

and run model again.

I should use the visual basic commands for solver or i should to using vb directly with open solver?

Can you include this parameter in your model as a decision variable with a penalty cost. If not you could use the Quick Solve feature with VBA code to repeatedly solve until you are feasible. or use Pulp or ampl in SolverStudio. Hope this helps. Andrew

Stack ID MN Ratio Tons

1 46 5.5 300

2 45 4.8 300

3 53 5.8 500

4 52 6.2 1500

5 45 5.2 400

6 46 6.2 500

Parameters MN>=46,Ratio>+5.5 Product1

MN>=46,Ratio>+5.0=46,Ratio=46,Ratio<5 Product3

How can I optimize the different products by combining the different stacks to get a maximum tonnage output for a specific product,i.e product1?

Hai guys,

I want to download an excel file from the site, I did it :-).

But when i click “Enable Editing”, it again downloads the same file from the server.

Wat may be the problem. Can u please help me

I don’t understand the question, sorry; OpenSolver does not have an “Enable Editing” feature. Andrew

Hey there, thanks for the great program

but i could not install this to my mac, is there a different software for mac ??

No Mac version, but we’d welcome some input to create one… Andrew

Thanks for this great tip! One last thing: I’ve noted that the number of variables is unlimited, however if the variables are in different selection slots the number of slots seems to be limited to 18 slots. I’ve tried giving a name to the whole range but only the 18 first selection slots are taken into account by OpenSolver. Would you see a way to work around this? Thanks a lot once again for your precious help and best,

Aziz

Just to thank you for a splendid piece of software. LP modelling really becomes practical now that one can extend models to reflect complex (large) real-life situations!

I have one question, though, is it possible to specify values in the variable cells from which OpenSolver can start a search for a solution? It appears to start with zeroes in the variable cells everytime and then keeps on bumping into a local optimum. I guess what I need is something that will allow evolution of the solutions…

Regards

Daniel Marais

Hi Andrew,

Thanks for the great job and for sharing Opensolver.

Would you please let me know if it possible to see which constrain is not respected when the solver can’t find any feasible solution? Thanks for your help and best,

OpenSolver cannot do this automatically, but I suggest that you add new decision variables that allow the constraints to be violated (and meaure this violation), and then minimise the sum of these violations. Hope this helps, Andrew

If there is another worksheet (with macros) open on the same instance of Excel, OpenSolver gives errors. It works fine if there are no other worksheets open. Any clues?

I have tried, but I cannot reproduce this, sorry. Could you please tell me what you were doing when the error happened, what error message you got, and a little more about the second worksheet you have open? Thanks, Andrew

Dear Admin

the first: I want to thank you, for your opportunities to have everyone the DEA Frontier Model, its help me very much to made my data research in my dissertation process.

the second: I have question: how many time I can use the DEA Frontier software (free option)because I need to have next view weeks or once mouth

Best Regard:

Jeton

Jeton: You need to direct DEA Frontier questions directly to them; we look after OpenSolver only (which DEA Frontier have chosen to make their software compatible with). Andrew

A specific question about Quick Solve: What’s the format for defining the “changing parameters”? Do they all need to be on the same sheet and contiguous?

The parameter cells are any range you can select using the standard Excel RefEdit control used by OpenSolver, which from memory restricts the cells to be on one sheet. They do not have to be contiguous. Cheers, Andrew

Great project.

Is there a roadmap for this add-in?

Especially adding sensitivity analysis would be very handy, but probably also very complex.

It is something we want to do if there is a demand, and CBC supports it (which I think the newer versions do). Do you just want it for teaching, or do you have a real-world application that needs this? Have you seen that the current version can give you the duals for each constraint? Andrew

Dear all,

Recently I have estimated a logaritmic model to estimate the price elasticities of one particular product. Now I try to use the solver to find the optimal price for each country. So I have price elasticity for each country. I want to make in the solver for example the next statement:

If I increase my price with 3 percent the quantity will decreace with 20 percent. How could such an statement be translated to solver.

Thank you in advance

Aydin Ramazanow

Aydin: I suggest you look up a textbook on modelling with linear and integer programming. Good luck, Andrew

I am using OpenSolver for a non-linear problem, but linearized iteratively. I need about 5-8 iterations to solve the problem. Each iteratative solve uses the same Excel ranges with different data.

I have noticed that setting up the problem in OpenSolver takes the longest time but solving is very quick. As I am using the same Excel ranges, is there a way to reduce the setting up time from the second iteration onwards?

There is indeed. The Quick Solve feature lets you set up the problem once, and remember how the left/right hand sides of the constraints change (assuming the changes are linear) as a set of “parameter” cells are changed. The steps are:

(1) build a model in which the left hand side or right hand side of one or more constraints depend on a set of “parameter” cells.

(2) Use “Set Quick Solve Parameters” to tell OpenSolver where the parameter cells are.

(3) “Initialise Quick Solve” to build the model

(4) Change the parameter cells, and then press the “Quick Solve” button to solve the model for the current parameter values.

Note that this only works for the constraint left/right hand side constants, so may not help you. In this case, I suggest you look at SolverStudio.org, as this has a much faster “build model” step.

Hope this helps,

Andrew

I am experimenting with QuickSolve and as far as I can tell I have to initialize the model everytime I open the file, correct?

Is there any way to save the file with the model initialized?

My problem has 9,000 variables and 12,000 rows and it takes about 25 minutes to Solve or to Initialize, but only a few seconds to quicksolve, so it would really help the users if the file opened “ready” to quicksolve.

Thank you and best regards

Mario

Mario, unfortunately, Quick Solve does not allow the model to be saved. However, SolverStudio has been created specifically for models such as your which are slow to build but easy to solve. Check out http://solverstudio.org; it’s a free download and still works with Excel, and removes the slow initialise/build process. Hope this helps, and thanks for your positive feedback. Andrew

I opted out of some of the software downloads that came with the shareware, and now it is not working. Do I have to agree to the spyware, or have I missed a step in the install?

OpenSolver is not shareware (it is open source), and does not include any other software downloads. Please use the download from this site to get a clean copy of the software untainted by any spyware or similar. (Your comment is most puzzling…) Andrew

Can any body help me. When I solve my model then this error occure…plzzzzzzzzzzzzz

OpenSolver could not find an optimal solution, but reported

No Solution found (unbounded)

A solution has been generated, but may be infeasible or sub-optimal or both

(CBC solver reported: Unbounded – Objective value 0.00000000)

Check your model carefully. Andrew

I’m experiencing the same problem, the model seems fine, and the function to solve is a simple linear equation. It seems that the solver does not search for parameters over 0. Possible?

OpenSolver considers all possible values for the decision variables. If you turn on the “assume non-negative”, it will consider positive values only. Check your model carefully. Andrew

i’m searching about the solver basic theory and algorithm but i still not understand about how excel works manually…can someone help me ? i need for my thesis…thank you

Hi, I have built many very large LP’s, and do so for a living. I have written a generalised front end for my models, which uses Excel to define the Classses of variables in the model, the members of these classes, and the tables which contain all of the data which populates the model. This process produces a large text file in MPS format, which is the whole LP for solving. My larger models have tens of thousands of variables and constraints, but I am looking for something that can be built into the middle of a Production Scheduling System utilising much smaller models at present. Can your solver(s) use MPS format input? This me http://www.linkedin.com/pub/alan-harrington/20/135/6b2

cheers,

Alan Harrington

The Excel solver gives a report of the shadow prices (dual solution) under LP mode. Can we get the same report with OpenSolver?

You can get the shadow prices (but no range information); check out the (relatively) new “Shadow Prices” option under the OpenSolver Model dialog. Cheers, Andrew

I am not able to open and use the opensolver.It is saying a message cant find solver kind of message.

I’m sorry it is not working for you. I suggest you remove any current version, and then try downloading it again. If that does not work, reparing Excel has worked for people. It is more likely to be a problem with your setup than with OpenSolver. Hope this helps, Andrew

Great Product, much better than anything else on the market

We are using version 1.9 run via the C# equivalent of RunOpenSolver true false. This works wonderfully for runtime exceptions. The problem is that this doesn’t seem to stop the “OpenSolver: Linearity check” message box from popping up. Moreover, the warning message… “Warning: The following constraints(s) do not appear to be linear:”… is incorrect. A simple “Run a full linearity check” will verify that the problem is in fact linear.

Any ideas? We really need to rid ourselves of message boxes for the product to be useful in a serious way.

Thanks for all your hard work, you’ve produced a gem!

I have come across this issue before, which seems to be caused by numerical issues. I will email you version 2.0 to test; it has two improvements. The first is to better estimate the accuracy of numerical calculations so as to avoid false reports of non-linearity. The second is to completely skip the non-linearity check. Please let me know if it fixes your problem (preferably without manually turning off the linearity check!). Andrew

I am also facing WARNING messages about non-linearity in the objective function as a consequence of excel numerical problems. Please, how could I seetin/configure Opensolver in order to not show this warning?

The next release will allow you to ignore these warnings. It is coming soon…. Andrew

Thanks. Much appreciated. Would you consider adding in one or a few examples to speed up the self-tutorial process? Happy New Year.

Hi Andrew! Great job! Easy to install. Nice button design.

I’m looking to use solver on a model which uses VBA to perform most of the calculations, variables and results are tracked on the worksheet. Unfortunatly Excel’s built-in solver cannot help with this… can opensolver?

Neither Solver or OpenSolver care how the values appear in the spreadsheet, as long as you can specify decision variables etc on the sheet. So, having calculations performed in VBA is fine as long as you are using User Defined Functions for these. If your calculations are run by a maco, then neither Solver nor OpenSolver will work. Further, if your calculations are complicated, they are probably not linear, and so the CBC solver used by OpenSolver will not work. If you are using VBA as a modelling lanaguage, then I suggest you look at SolverStudio (http://solverstudio.org) which handles modelling languages (currently PuLP, with others coming soon). Hope this helps, Andrew

Re: Calculations run by a macro.

My workaround is to use the Change event of the worksheet that contains the cell(s) that Solver is using.

It runs every time a cell value on the worksheet (“Main_sheet”) changes. If the cell is one of Solver’s change cells, the calculations macro runs (Sub MainA)

The key part is that while the calculations macro runs, Events are turned off. After the calcs are printed in the worksheet by MainA, Events are turned back on and Solver will react for its next iteration.

Hope this is useful. I’ve only used this with built-in Frontline Solver.

Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim adjustCells As String

On Error Resume Next

Application.EnableEvents = False

adjustCells = SolverGet(typeNum:=4, sheetname:=”Main_sheet”) ‘Solver changing cells

If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then

Call MainA ‘MainA does the calcs. Only runs if change is in Solver change cells

End If

Application.EnableEvents = True ‘now Solver will fire

End Sub

Mike, Thanks for that tip, which is a good way to do something complicated in a (presumably non-linear) model. I would not have thought that disabling events was crucial, but I have not tried it. Another way would be a user-defined function which also allows VBA code to execute during a Solver run if the input variables to the function include the model variables. (Readers should note that OpenSolver only does linear models, for which these tricks are not so useful.)

Cheers, Andrew.

PS: Now we know that Solver is the reason why your boats are so fast…

Solver: couldn’t live without it! I’ve only just seen your project and will give it a go.

I notice there is a typo or maybe Web display problem in the above code, the correct line is:

If InStr(1, adjustCells, Target.Address, vbTextCompare) 0 Then

in case it’s a display thing:

If Instr(….) not_equal_to 0 Then

It’s about a decade since I figured this out, and I’m pretty sure turning off Events while the calc macro runs is important because Events are recursive, so the first result printed by the macro on the worksheet will fire Solver (I think); which will run before the calc macro finishes printing the full results.

At least that’s how I remember the problem.

My main use of this is to solve for geometry features by running a NURBS macro.

Mike

Mike

Need help!

I am trying out to find out the minimum possible manpower for the required work load.

I am selecting the decision variables directly using mouse but the variables are gone when I reached the 34th variable. What happened? I’m using Excel 2007.

Anyone?

There is a maximum limit set by Microsoft on the number of characters allowed in the decision variables RefEdit text box. You may be encountering this? We have increased the size of this text box to allow more decision variables to be entered and displayed. I would suggest you group your decision variables together so they can be entered in one go, or create new decsion variables in one group, and refer to these in your current decision variable cells. Hope this helps. Please let us know how you get on. Andrew

Yes, you are right with the RefEdit text box. It work! Thanks!

What a wonderful project! Hope to continue growing…

… Is there a way to use it from vb.net express?

Congratulations!!

Thanks for your positive feedback. Yes, you can run VBA macros (include OpenSolver’s) from within a .net language by (1) first loading the OpenSolver add-in, and then (2) calling a macro in the workbook, using code such as Application.Run(“OpenSolver.RunOpenSolver”). (This comes from a VSTO add-in, where Application is defined as: internal Microsoft.Office.Interop.Excel.Application Application.) Please let me know how you get on; I’m sure other users would be interested. Andrew

I have reached the limit of the standard Excel LP solver and OpenSolver seems to be very promising. One small question – Can OpenSolver be manupulated by VBA?

Yes, OpenSolver is VBA-friendly. You can edit models just as you do with Solver (because they are Solver models), and then optimise them using the VBA code at http://opensolver.org/using-opensolver. Hope this helps, Andrew.

Can your tool handle three subscripts for variables? It looks very helpful and I love that you can do large problems.

Thanks!

OpenSolver just sees a list of variables on your spreadsheet, so it doesn’t care how many subscripts there are. If you are thinking about subscripts, then it sounds like you are using a modelling language – maybe you should look at OpenSolver’s companion product, http://opensolverstudio.org, which supports modelling languages such as PuLP (and, in a coming release, AMPL). Andrew

We are using the opensolver to optimize water conservation. The problem uses the number of water using accounts at varying levels of consumption to apply water conserving best management practices, to get the most water conservation for the cost of the BMP. However, when we try to apply the model to individual accounts, we will end up with ~4M variables, including constraints. Opensolver seems to begin solving the problem, but it looks like it will take 155+ of processing time. Is there any way to speed this up. I am using a 32 bit machine and the latest version of opensolver. I think the premium solver has an option which disregards all of the obvious solutions to shorten the run-time. Any ideas?

Max: That sounds like a really interesting problem you are solving. Can you please explain it in a little more detail. Can I confirm that you have a spreadsheet with 4 million decision cells on it? Is it an LP or an IP that you are solving? When you say OpenSolver starts solving the problem, I assume it is in the building phase (when you can see it stepping thru all the decision cells, as displayed in the Excel status bar), not in the running the external optimizer phase? Perhaps if you email me the spreadsheet, I can take a look… a dot mason at auckland dot ac dot nz. Thanks, Andrew

A bit more info. OpenSolver performs a slow tedious process to determine the model. The Frontline products that include their Psi engine can by-pass this step, and so will be faster. However, if you have a problem with 4 million variables, then I’d suggest you look at OpenSolver Studio (http://opensolverstudio.org), which allows you to build a model separately from the data. This should be much faster for large problems. OpenSolver Studio will be adding more modelling languages as it matures; at the moment it comes with PuLP. By the way, can you confirm that your model is a linear one? Are there really 4 million cells selected as decision cells (ie adjustable cells) in your model? Are these binary or continuous variables? How many constraints are there? Andrew

Andrew,

I am sending a copy of our spreadsheet that contains fewer decision variables. Most of our utilities have ~20,000 accounts. Each account currently has 14 decision variables. That’s 280,000 total. Our largest utility has almost 300,000 accounts, which would result in about 4M decision variables.

Andrew,

we sent the data yesterday. We have been playing around with PuLP and ran a smaller model with ten accounts, successfully. We were trying to incorporate the Open Solver – Model Data Items tool into the PuLP set of instructions, and were unable to make it work. We will be working towards accomplishing this while waiting for your response. Great tool!

Thanks in advance!

Max

I am going to try it I will share my experince .

Hi,

thank you so much for programming the OpenSolver. It really helped me out when I had reached the limits of the standard solver.

Best Regards,

Richard

Richard: Thanks for the positive feedback; it is really appreciated by the OpenSolver team. Andrew

Thanks for your great work! OpenSolver is THE way to go if you are bounded by the restrictions of the regular Excel solver. Keep up the good work! Kind Regards, Team EasyExcelTutorial.com

Yours is a very usefull tool!

I might suggest that it is important to save models and, later, load them…

When variables are too many, they fills the space of “variable cells” field. Often, the name of sheet, inside constraint fields, could fire some exception.

These problems, however, are all surmountable.

Thanks for your comments. Your feedback on the variable cells has been most useful; the next version will have a larger space for these. Can you please give more details on the exception firing problem? We would like to fix that. You can still use Solver to save and load models if you need to do that. Cheers, Andrew

Thank you for your fast reply.

About error due to the sheet name: suppose “a b” the sheet name. So, if I use the wizard of OpenSolver to create constraints I select cells with the mouse… Inside the LHS I have, for example, ‘a b’!$C$14, and inside RHS I have ‘a b’!$C$15 (with operator <=). If I click on "Add Constraint" an error message appears: "The formula or value for RHS is not valid. Please check and try again.". So, I usually delete " 'a b'! " from both fields and the adding action work.

Sorry for my double reply (and for my hazardous English)! But, I just thought that Cbc solver, with an appropriate configuration of the logging system, writes how many nodes have been opened and their information, like the lower bound, the timestamp, ecc…

So, if I wanted to evaluate, for example, the trend of solution value, I would read log…

What do you think about?

Andrea, Sorry; I missed your post before. The log is written to the C “standard out”; you would need to capture this (or pipe it to a file) if you want to analyse or display the log information. Let me know if you succeed in making this work. Cheers, Andrew

Hi,

When I click solve button on OpenSolver 1.4, I always get this message:

“Model Solve Failed: The CBC solver did not create a solution file. No new solution is available.”

Regards,

Hi Mauricio,

Please try OpenSolver 1.5, we think it will solve the issue.

Hi Iain,

Thanks, OpenSolver 1.5 works fine!!

Congratulations!!! FANTASTIC!!! It’s very easy, faster and more precise than Premium Solver

last week i was running standard solver in Excel 2007…. 14 hours of processing… with OpenSolver…. only 1 (one) minute!!!!!

Thank you very much…!!!

Cristian

from Chile

Hi Andrew,

Thank you very much for this mammoth efforts to create this fantastic application!

It helped our organization to solve Mixed Integer Linear Programs of around 5000 variables and 6000 constraints very quickly, without buying premium solver. Also, it is very easy to integrate it with Excel’s existing solver and also to automate in VBA. I found that OpenSolver always provides as good solution as premium solver and sometimes produces even better solutions, that too with considerably less time and memory usage.

Once again, thank you very much!!!!

I am pleased that OpenSolver has been useful. Thanks for the feedback. Andrew

Hello Ashwin,

I am trying to do the same. Can you please help me in that. Is it possible to import a lp file to the opensolver? Also can you please send me the model.

Thanks!!

Hello Ashwin,

I am trying to do the same. Can you please help me in that. Is it possible to import a lp file to the opensolver? Also can you please send me the model.

Thanks!!

I’ve used this for a scheduling problem that I’ve encountered. It had tons of variables that the default add-in couldn’t handle but open solver could! Great work guys!

great work! this is an awesome project and great to see a solid open-source optimization option for excel.

just a quick friendly reminder about opensolver for office 2010. Thanks for your great job!