Hi,
I saw the recent comment about alldifferent constraints and I was also trying to implement a model using alldifferent (sudoku) in OpenSolver but I get an error message (I’m assuming it’s because alldifferent isn’t implemented). One way you could implement alldiff in OpenSolver is to translate it into the corresponding assignment-like linear model when creating the .lp file. For example: for every variable Xi that’s integer between 1 and n, create n binary variables Yi1,…Yin, have an assignment formulation on the Yin variables (\sum_{i} Yij = 1 for all j, and \sum{j} Yij = 1 for all i) and, for each i, say Xi = \sum_{j=1}^n j*Yij.
Best,
Tallys
Tallys, Thanks for that suggestion. We have briefly considered this, but as you say, we have not implemented anything yet (and hence the error you get). I will add it to the TODO list. But in the mean time, people can use a model of the form you have suggested. Cheers, Andrew
Why has OpenSolver not developed an ‘alldiff’ function? It is a nightmare to program a basic Travelling Salesperson Problem directly in Excel, but it is simple to program in VBA.
Thanks for your generosity in providing us with this very nice free tool. May I ask a couple of straightforward questions? All refer to the context of using *OpenSolver in VBA*. I should be grateful if you could answer them:
– How do we set the time limit?
– How do we set the optimality gap?
– How do we show the user the window with the cbc iterations?
– How can we obtain the dual values from the optimal solution?
Firstly, I much prefer to build the model using OpenSolver, and then just solve it using VBA. (You can normally design a single model that is flexible enough to handle a range of problems.) However, if you really must build the model using VBA, then I note that OpenSolver tries to respect the optimisation settings stored in the sheet. These can be editted from VBA using the Solver VBA calls. Dual values can be obtained (in the later OpenSolver releases) by writing these to the sheet (using the Model dialog settings). You can then access them from the sheet using VBA. Hope this helps. Andrew
Thanks for your prompt reply and directions.
I am aware of this direction/preference on minimizing VBA use where possible – you have mentioned it in previous threads as well. In the context I am working the user will determine the size of the problem: for instance, the user will input the number of scenarios (e.g. forecasted demand values), or the number of machines/jobs in a machine scheduling problem etc. I guess a preset template is not possible in this case and the VBA would be the only option to go. I would appreciate your confirmation to check whether my understanding is correct.
If you cannot simply allocate some maximum size to a problem, then yes, VBA will be required. However, SolverStudio is another option that can easily handle problems of different sizes (using named ranges that contain offset() formulae). That might be worth considering, particularly if you are building big models. Cheers, Andrew
I hav been using OpenSolver 2.1 for some time now and it has been really helpful.
Today I downloaded version 2.5.4 and tried to solve my models with Gurobi using OpenSolver. However, I ran into some problems. The output provided for the binary and integer variables is correct. For the continuous variables however, something seems to go wrong. The solution provided is correct, but the decimals are off. For example: a variable which should be 5 has as output 499999999999909. What could be going wrong here? The calculation time improves significantly when using Gurobi, so I hope I can get this to work properly.
I was able to fix the problema, it had something to do with my computer. My new problema is that when I run OpenSolver, I need the variables to be positive, they cant be negativo, so I check the box where it says, Make unconstrained variables non-negative, but it keeps on putting negative values. What should I do? Once again, thanks a lot.
I’ve noticed a problem.
If there is a circular reference that requires iterative calculations to solve, anywhere in my workbook, then OpenSolver will crash. The circular reference does not need to be part of the formulation, influence the objective function or variables, or even be on the same worksheet. Something about Excel’s need to calculate the reference iteratively prevents it from working.
I’m running excel 2010 32bit. Let me know where to send an example
Thanks for this bug report, which is a new issue we have not considered before. I have asked our programmer to look into this. Many thanks for reporting this. Thanks, Andrew
I tried to solve my model which a linear programming (LP) model and I run in using both; (Excel Solver and OpenSolver)to get the solutions and then, I compared the solutions but, why the results differ?
Is it because of the OpenSolver could not solve the linear programming (LP) and just work with integer linear programming (ILP)?
This means that OpenSolver can solve the LP model as well? I thought earlier, it can only solve ILP model. So,I can use both solver without any confusion on the solutions anymore~ Thank you for this great tool again.
But, sorry again.. I have few questions to ask~
How to find other alternate solutions? and,
Is that possible for me to view the solution step by step?
Luckily I found this tool. Now, I can finish my work. I can’t afford for such an expensive solver since I’m just a student. Thank you for your hard work building this great tool. Really appreciate it~ Thanks Andrew.
I am using your solver to solve a very large (but linear) problem, with two runs of ~ 10,000 – 100,000 variables. Solving the model is almost instantaneous, however setting up the problem takes a while. On the first run, the rate of “set-up” is around 1000 variables per second (indicated by the “Setting up problem… 1000/45000 vars, 800 rows” display). This speed is fairly reasonable, as the problem is not time sensitive. However, on the second run (exact same model, around the same number of variables), this rate slows to a crawl (20 vars/second). Do you have any idea what is causing the slowdown? I would think this is a memory issue, in which case could it the first Solver run that is hogging memory? Is it possible to clear the memory before the second run?
That is strange. Are you perhaps adding a worksheet to the workbook after you solve the first time? If you like to email your spreadsheet to a.mason at auckland dot ac dot nz, then I will try to have a look at it over the next few weeks. Andrew
I was just wondering how many constraints open solver can handle. I’m working with Excel 2010 and the excel solver can only accept 100 constraints, whereas I would need at least 200…
I had so many cells that I decided to start over. Now, the model is running perfectly. Most likely, I had a mistake in any operation, loosing linearity. It is a big model, I tried with all integer variables but there were difficulties coming up an solution, so later, I relaxed partially and it worked out !!!
Please check (1) that your model is linear (eg, does it evaluate correctly when all decision variables are 0?), and (2) that any constraints entered with a left hand side as columns (or rows) have a right hand that is either a single cell, or a column (or a row if the left hand side is a row) of the same size. Hope this helps, Andrew
I have a problem with a model. It says that “no coinciden los tipos” at line 13700 and at line 19540. ErrNumber = 13. What does Errnumber = 123 mean? How Can I identify lines 13700 and 19540 ? Thanks a lot
Are you sure your model is linear (no IF’s in it, for example)? If so, this may be an error we have not seen before; can you please email your spreadsheet to email hidden; JavaScript is required Thanks, Andrew
Did you find any solution for this problem? I get the same error in my model even though I’ve tried to exclude all the IF’s. Hope to hear from you soon /Henrik
I’m testing a 2-D Bin packing problem. It has 25 items and 650 binary variables (up to 25 bins). When I view the progress, the cmdprompt screen pops up letting me know how the CBC is going line by line. However, the application stops working. It says “This application has requested the Runtime to terminate in an unusual way. Please contact the application’s support team for more information.” The problem seems to solve on smaller instances, i.e., when there are 20 items (and 20 bins) or fewer.
I’m running Excel 2010 on Windows 7 desktop 64bit Dell T5500 with the latest version of OpenSolver.
This would appear to be a problem with CBC. Have you tried manually installing the latest version of CBC (replacing the one that came with OpenSolver)? You should use a 64 bit version as you have a 64 bit machine. There is a link to CBC Download on opensolver.org. Let me know how this goes. Andrew
Hi,
I saw the recent comment about alldifferent constraints and I was also trying to implement a model using alldifferent (sudoku) in OpenSolver but I get an error message (I’m assuming it’s because alldifferent isn’t implemented). One way you could implement alldiff in OpenSolver is to translate it into the corresponding assignment-like linear model when creating the .lp file. For example: for every variable Xi that’s integer between 1 and n, create n binary variables Yi1,…Yin, have an assignment formulation on the Yin variables (\sum_{i} Yij = 1 for all j, and \sum{j} Yij = 1 for all i) and, for each i, say Xi = \sum_{j=1}^n j*Yij.
Best,
Tallys
Tallys, Thanks for that suggestion. We have briefly considered this, but as you say, we have not implemented anything yet (and hence the error you get). I will add it to the TODO list. But in the mean time, people can use a model of the form you have suggested. Cheers, Andrew
Why has OpenSolver not developed an ‘alldiff’ function? It is a nightmare to program a basic Travelling Salesperson Problem directly in Excel, but it is simple to program in VBA.
Not sure why you say it is easy in VBA. OpenSolver has no alldiff() as the CBC solver has no alldiff(). Please correct me if CBC can do this. Andrew
Hello Andrew,
Thanks for your generosity in providing us with this very nice free tool. May I ask a couple of straightforward questions? All refer to the context of using *OpenSolver in VBA*. I should be grateful if you could answer them:
– How do we set the time limit?
– How do we set the optimality gap?
– How do we show the user the window with the cbc iterations?
– How can we obtain the dual values from the optimal solution?
Many thanks in advance.
George
Firstly, I much prefer to build the model using OpenSolver, and then just solve it using VBA. (You can normally design a single model that is flexible enough to handle a range of problems.) However, if you really must build the model using VBA, then I note that OpenSolver tries to respect the optimisation settings stored in the sheet. These can be editted from VBA using the Solver VBA calls. Dual values can be obtained (in the later OpenSolver releases) by writing these to the sheet (using the Model dialog settings). You can then access them from the sheet using VBA. Hope this helps. Andrew
Andrew,
Thanks for your prompt reply and directions.
I am aware of this direction/preference on minimizing VBA use where possible – you have mentioned it in previous threads as well. In the context I am working the user will determine the size of the problem: for instance, the user will input the number of scenarios (e.g. forecasted demand values), or the number of machines/jobs in a machine scheduling problem etc. I guess a preset template is not possible in this case and the VBA would be the only option to go. I would appreciate your confirmation to check whether my understanding is correct.
Thanks again,
George
If you cannot simply allocate some maximum size to a problem, then yes, VBA will be required. However, SolverStudio is another option that can easily handle problems of different sizes (using named ranges that contain offset() formulae). That might be worth considering, particularly if you are building big models. Cheers, Andrew
Hella, thanks for this fantastic tool.
I hav been using OpenSolver 2.1 for some time now and it has been really helpful.
Today I downloaded version 2.5.4 and tried to solve my models with Gurobi using OpenSolver. However, I ran into some problems. The output provided for the binary and integer variables is correct. For the continuous variables however, something seems to go wrong. The solution provided is correct, but the decimals are off. For example: a variable which should be 5 has as output 499999999999909. What could be going wrong here? The calculation time improves significantly when using Gurobi, so I hope I can get this to work properly.
Thanks,
Joost
This is probably an internationalisation issue that should be easy to fix. I will email you for more details. Andrew
Hello and thanks for your software. Ive been having a problema with my worksheet that makes Excel crash. It says this:
software exception 0xc0000417
ubication 0x744b20f0
Then it closes Excel.
Thanks for your help.
I was able to fix the problema, it had something to do with my computer. My new problema is that when I run OpenSolver, I need the variables to be positive, they cant be negativo, so I check the box where it says, Make unconstrained variables non-negative, but it keeps on putting negative values. What should I do? Once again, thanks a lot.
Hi,
I’ve noticed a problem.
If there is a circular reference that requires iterative calculations to solve, anywhere in my workbook, then OpenSolver will crash. The circular reference does not need to be part of the formulation, influence the objective function or variables, or even be on the same worksheet. Something about Excel’s need to calculate the reference iteratively prevents it from working.
I’m running excel 2010 32bit. Let me know where to send an example
Thanks,
Max
Thanks for this bug report, which is a new issue we have not considered before. I have asked our programmer to look into this. Many thanks for reporting this. Thanks, Andrew
Hi, thanks for this amazing tool
I have questions here.
I tried to solve my model which a linear programming (LP) model and I run in using both; (Excel Solver and OpenSolver)to get the solutions and then, I compared the solutions but, why the results differ?
Is it because of the OpenSolver could not solve the linear programming (LP) and just work with integer linear programming (ILP)?
Many problem have multiple optimal solutions with the same objective function value.
Oh, okay~ thank you Andrew for the reply~
This means that OpenSolver can solve the LP model as well? I thought earlier, it can only solve ILP model. So,I can use both solver without any confusion on the solutions anymore~ Thank you for this great tool again.
But, sorry again.. I have few questions to ask~
How to find other alternate solutions? and,
Is that possible for me to view the solution step by step?
Thanks~
Finding alternate solutions is hard. You can view the CBC output by using the OpenSolver Model Options dialog.
okay~ Thank you Andrew.
Luckily I found this tool. Now, I can finish my work. I can’t afford for such an expensive solver since I’m just a student. Thank you for your hard work building this great tool. Really appreciate it~ Thanks Andrew.
Hi Andrew,
Thank you for this amazing tool.
I am using your solver to solve a very large (but linear) problem, with two runs of ~ 10,000 – 100,000 variables. Solving the model is almost instantaneous, however setting up the problem takes a while. On the first run, the rate of “set-up” is around 1000 variables per second (indicated by the “Setting up problem… 1000/45000 vars, 800 rows” display). This speed is fairly reasonable, as the problem is not time sensitive. However, on the second run (exact same model, around the same number of variables), this rate slows to a crawl (20 vars/second). Do you have any idea what is causing the slowdown? I would think this is a memory issue, in which case could it the first Solver run that is hogging memory? Is it possible to clear the memory before the second run?
Any help is much appreciated.
Best,
C
I should also mention that Solver is being called from VBA.
That is strange. Are you perhaps adding a worksheet to the workbook after you solve the first time? If you like to email your spreadsheet to a.mason at auckland dot ac dot nz, then I will try to have a look at it over the next few weeks. Andrew
Hi,
I was just wondering how many constraints open solver can handle. I’m working with Excel 2010 and the excel solver can only accept 100 constraints, whereas I would need at least 200…
Many thanks,
Samuel
Excel limit at 100 is ridiculously low. The opensolver kan handle a lot more than that. My model had at least 2000 with no problem.
Thanks again for a lovely tool.
I had so many cells that I decided to start over. Now, the model is running perfectly. Most likely, I had a mistake in any operation, loosing linearity. It is a big model, I tried with all integer variables but there were difficulties coming up an solution, so later, I relaxed partially and it worked out !!!
Any progress regarding error 13? What does this error mean?
“OpenSolver encountered error 13: Type mismatch (at line 9480) (at line 19660) Source = OpenSolver”
Please check (1) that your model is linear (eg, does it evaluate correctly when all decision variables are 0?), and (2) that any constraints entered with a left hand side as columns (or rows) have a right hand that is either a single cell, or a column (or a row if the left hand side is a row) of the same size. Hope this helps, Andrew
Feature Request:
Thanks for the wonderful tool. It would be even better if OpenSolver can leverage the warm-start capability CBC finally picked up.
I haven’t followed this. How can we use it from the command line? I assume you are talking about IP, not LP? Andrew
I have a problem with a model. It says that “no coinciden los tipos” at line 13700 and at line 19540. ErrNumber = 13. What does Errnumber = 123 mean? How Can I identify lines 13700 and 19540 ? Thanks a lot
Are you sure your model is linear (no IF’s in it, for example)? If so, this may be an error we have not seen before; can you please email your spreadsheet to email hidden; JavaScript is required Thanks, Andrew
Any progress regarding this? I have the same problem.
Did you find any solution for this problem? I get the same error in my model even though I’ve tried to exclude all the IF’s. Hope to hear from you soon /Henrik
I’m testing a 2-D Bin packing problem. It has 25 items and 650 binary variables (up to 25 bins). When I view the progress, the cmdprompt screen pops up letting me know how the CBC is going line by line. However, the application stops working. It says “This application has requested the Runtime to terminate in an unusual way. Please contact the application’s support team for more information.” The problem seems to solve on smaller instances, i.e., when there are 20 items (and 20 bins) or fewer.
I’m running Excel 2010 on Windows 7 desktop 64bit Dell T5500 with the latest version of OpenSolver.
Great product: thanks!
This would appear to be a problem with CBC. Have you tried manually installing the latest version of CBC (replacing the one that came with OpenSolver)? You should use a 64 bit version as you have a 64 bit machine. There is a link to CBC Download on opensolver.org. Let me know how this goes. Andrew
OpenSolver fails to comply with restrictions
Can you please be more specific? Andrew