SolverStudio 0.5 Released, now with Cloud-based Optimisation
SolverStudio 0.5 is available, and now brings cloud-based optimisation to Excel. 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 SolverStuduio 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.
OpenSolver uses the COIN-OR CBC optimization engine
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. 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.
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.
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]\AppData\Local\Temp\model.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 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!