25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.NumberOfOperands: Line 8138
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.PopOperator: Line 8210
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ConvertFormulaToExpressionTree: Line 8106
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ProcessSingleFormula: Line 7796
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ProcessFormulae: Line 7793
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 7468
25 jan 16 15:43:18 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 0
25 jan 16 15:43:18 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
25 jan 16 15:43:18 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220502: Unknown function transpose
Please let us know about this at opensolver.org so we can fix it.
Solver: Bonmin
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.2 with VBA6 in 32-bit Excel 12.0
CBC v2.9.4 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\cbc.exe”
No Gurobi installation was detected. The value of GUROBI_HOME was not set.
Bonmin v1.8.1 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\bonmin.exe”
Couenne v0.5.3 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (32-bit) using OpenSolverNomad v1.2.2 at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win32\OpenSolverNomad.dll”
I have a couple of more questions about OpenSolver 2.7.1:
(1) In Solver, you can specify non-contiguous cells for the variables by separating those cells with a comma in the Variable Cells box (for example, B2, D7). Am I correct that OpenSolver does not allow the use of a comma in the Variable Cells box and so all variable MUST BE CONTIGUOUS?
(2) Am I also correct that OpenSolver does not return the Answer Report that Solver generates and so with OpenSolver we do not get the values of the slack variables?
Thanks again for your time in answering these two questions.
Hold down control to select multiple cells for the variables. I have never found the answer report useful; you can calculate slack variables trivially on the sheet. Andrew
I have two questions about using OpenSolver 2.7.1:
(1) When solving an LP from VBA, Solver returns a numerical value to indicate whether the LP is infeasible, optimal or unbounded. Does OpenSolver do the same? If so, how do I capture that numerical value? On a related note, is there some way in VBA to surpress the message box from OpenSolver when the LP is infeasible?
(2) When solving an integer program, is there any way to provide OpenSolver with an initial integer feasible solution whose objective function value COIN-OR can then use to improve computational efficiency? If so, how is this done?
Thanks for your prompt response and congratulations on putting together a wonderful package…you have made a great contribution. I intend using OpenSolver in two of my courses this coming fall.
I am getting an error while running OpenSolver2.7.1 add-in on excel 2013 64-bit windows 7 64-bit
When I try solving the model I have build, a pop-up is coming with title ‘C:\WINDOWS\System32\cmd.exe’ which is not going.
When I try closing the pop-up manually it gave me the following error:
“OpenSolver 2.7.1 encountered an error:
The CBC solver did not complete, but aborted with the error code -1073741510.
The last log file can be viewed under the OpenSolver menu and may give you more information on what caused this error.
An error log with more details has been saved, which you can see by clicking ‘More Details’. If you continue to have trouble, please use the ‘Report Issue’ button or visit the OpenSolver website for assistance:”
Clicking on ‘More Details’ directed me to the error log, which gave me the following information:
“22 Dec 15 13:19:31 [OpenSolver.xlam] SolverCommon.RunLocalSolver: Line 0
22 Dec 15 13:19:31 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
22 Dec 15 13:19:31 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220501: The CBC solver did not complete, but aborted with the error code -1073741510.
The last log file can be viewed under the OpenSolver menu and may give you more information on what caused this error.
Solver: CBC
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 64-bit Excel 15.0”
I would like to congratulate you and all the team for such an amazing project!
I am working on the development of production planning models using OpenSolver and, unfortunately, I am facing some issues when trying to solve them.
The first one is this:
This week I tried to solve one model and this message appeared at the screen:
OpenSolver 2.7.1 encountered an error:
Unable to delete the file: C:\Users\ube8957\AppData\Local\Temp\log1.tmp
What does it mean?
The second is this:
I have built some models and solved all of them with no problem. When the first problem came up I tried to solve these models I had already tested in order to see if the problem was only happening with the model with the first problem, but the solver could not find the optimal solution for most of them. Here is the message:
OpenSolver could not find an optimal solution, and reported:
Stopped on iteration limit.
It looks like I have to increase the iteration limit, but, when I used to solve these models there was no problem and the number of iterations was the same. So it does not make sense to me.
Even so, I increased the iterations limit and had no success.
I am pretty sure these models were feasible and that their structures are ok, because I have reviewed them a lot of times.
I would like to have some insights on how to solve them, please.
Antonio: Sorry to hear of your problems.
The error “Unable to delete the file: C:\Users\ube8957\AppData\Local\Temp\log1.tmp” typically indicates taht Excel or OpenSolver crashed, leaving a file open; please (1) try quitting and restarting Excel (which is unlikley to work), and then (2) restarting your PC to close the file.
Re the iteration limit, newer versions of OpenSolver enforce this limit, which was not the case in earlier versions, and so increasing the limit is the right thing to do. However, you say this did not fix the problem; can you be more specific? Is the solver saying the model is infeasible? Are you sure the model is linear? Non-linear models can cause unpredictable results as we change OpenSolver (and ideally should always fail to solve, but we cannot guarantee this). Andrew
We are aware of the potential issues with SourceForge, and we certainly do not agree with their tactics. However, as OpenSolver is an active project, we seem to be safe at the moment. Finding an alternative is on our ToDo list. Thanks, Andrew
Hi there. Is there sample code for running OpenSolver through VBA? I pieced together some code from the feedback thread, but am running into issues (particularly with SetDecisionVariables at the moment). Thanks.
Here’s what I have:
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“Summary”)
On Error GoTo errHandler_NoOpenSolver
Application.Run “OpenSolver.xlam!RunOpenSolver”
On Error GoTo errHandler
Exit Sub
errHandler:
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
errHandler_NoOpenSolver:
Err.Clear
MsgBox “This workbook requires OpenSolver, a free Excel addin available at http://opensolver.org” + vbCrLf + vbCrLf + “Please install & then open OpenSolver, and then try again.”, vbOKOnly, “OpenSolver”
I am trying to automate the work on excel by running Opensolver from outside excel with python application OpenPyXL. I would like to know whether it is possible to kickstart opensolver the same way that RunOpenSolver does in VBA
You can generate a click on the Solve button; see SolverStudio.org for how to do this. Otherwise, I’m not sure; please let us know how you get on. Andrew
I see now that OpenPyXl appears to be only for reading/writing s/sheets. If Excel is not running, there is no way to run OpenSolver. If you want to use Python in Excel, I strongly suggest you look at SolverStudio http://solverstudio.org Cheers, Andrew
Dear Andrew,
can you suggest a way to link OpenSolver (instead of Frontline Solver) with SolverTable (http://kelley.iu.edu/albright/free_downloads.htm) to create parametric linear programs and solve them sequentially? Thanks!
You’d have to change the SolverTable’s VBA code. We have a long term plan to add something similar to OpenSolver, but have not yet done so. Andrew Mason
Hi!
My name is Aline and I am a student of Industrial Engineering.
I am using the OpenSolver 2.1 to do optimization tests for an academic project I have worked on applied to a Textile Industry.
I would like to know if there is any way that I can visualize the execution times for each optimization test performed. Is there any way that I could do that?
Thank you very much,
Aline.
Aline: I’d do the timing using VBA, or you might find some timing output in the CBC logs (but I’m not sure – you’d have to experiment with logging options). Good luck, Andrew
What is the maximum number of variables and the maximum number of constraints that open solver can solve. And is @RISK better than open solver? If yes then how? Please reply asap.
I just installed the OpenSolver Add-In with Excel 2013 Pro x64 and the model window looks like this: http://i.imgur.com/ImSImQG.png
If I click around where the text boxes should be, they appear. If I select too many constraints everything freezes up. If I try to click on the cell selection button to the right of the text boxes nothing happens.
Did I install it wrongly? I copied everything from the ZIP file to the addons directory and activated it in Excel. Are there necessary steps I missed?
Christoph: Your “refedit” control in Excel is broken; please try repairing your Excel installation. Google for instructions for your version of Excel. Hope this helps, Andrew
Sorry it’s not working. Please tell us more… Windows or Mac? Which Solver are you using? When it is running out of memory… while building the model or while running the solver? How big is your model?
1. Am working on Version 2.7.1 and Windows Excel 2003. I am getting the error message while running the solver.
The data set is quite huge with around 875000 rows which also made excel processing quite slow. And then the constraints set requires formula calculation. Is using excel and open solver appropriate for such a variant?
2. Am adding another issue am facing for the same model ( but with a changed formula in cells referred in constraints) I ran with a smaller set of the data. Earlier the cell values were hard coded but I had to make it dynamic. So, I added cell value as =VLOOKUP($P$5&”|”&O6&”|”&$A$1,$A$5:$L$400,6,FALSE). But this gives an error on running the solver as mentioned below:
“Error -2147220502: The cell ‘P6’ in constraint ‘P6:X15 = P20:X29’ has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!). Please fix this and try ”
1. Am working on Version 2.7.1 and Windows Excel 2003. I am getting the error message while running the solver.
The data set is quite huge with around 875000 rows which also made excel processing quite slow. And then the constraints set requires formula calculation. Is using excel and open solver appropriate for such a variant?
2. Am adding another issue am facing for the same model ( but with a changed formula in cells referred in constraints) I ran with a smaller set of the data. Earlier the cell values were hard coded but I had to make it dynamic. So, I added cell value as =VLOOKUP($P$5&”|”&O6&”|”&$A$1,$A$5:$L$400,6,FALSE). But this gives an error on running the solver as mentioned below:
“Error -2147220502: The cell ‘P6’ in constraint ‘P6:X15 = P20:X29’ has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!). Please fix this and try ”
Do you really mean 875,000 rows in Excel 2003? I’m impressed that Excel is managing with such a large spreadsheet. If that gives a model with 875,000 constraints, then I’d be very surprised if it worked in OpenSolver without running out of memory. Try SolverStudio instead (but even that will struggle I suspect). Regarding your model and the OpenSolver error, you may have a non-linear model, which CBC cannot solve, and is probably too big for NOMAD (the only non-linear solver you could consider). Sorry I cannot be of more help. Andrew
I am using Open solver to optimise the harvest schedule in a forest estate. To test various harvest options I need amend the constraints on a regular basis. However Open solver is cumbersome at present as you need to re-enter constraints after each run when some have been deleted/amended etc. It would be great to have the availability of a ‘check box’ to switch the various constraints on and off. thanks,
Peter. Nelson NZ
Peter: Great to hear that OpenSolver is helping NZ forestry. Yes, it is indeed cumbersome; adding checkboxes to turn on/off constraints is on our ToDo list (ideally in a way that lets users do this from the spreadsheet). In the meantime, you can adjust right hand sides to make constraints inactive; that’s what I do. Cheers, Andrew
is there any help for this problem?
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.NumberOfOperands: Line 8138
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.PopOperator: Line 8210
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ConvertFormulaToExpressionTree: Line 8106
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ProcessSingleFormula: Line 7796
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.ProcessFormulae: Line 7793
25 jan 16 15:43:18 [OpenSolver.xlam] SolverFileNL.SolveModelParsed_NL: Line 7468
25 jan 16 15:43:18 [OpenSolver.xlam] SolverCommon.WriteModelFile: Line 0
25 jan 16 15:43:18 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
25 jan 16 15:43:18 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220502: Unknown function transpose
Please let us know about this at opensolver.org so we can fix it.
Solver: Bonmin
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.2 with VBA6 in 32-bit Excel 12.0
CBC v2.9.4 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\cbc.exe”
No Gurobi installation was detected. The value of GUROBI_HOME was not set.
Bonmin v1.8.1 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\bonmin.exe”
Couenne v0.5.3 (64-bit) at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win64\couenne.exe”
NOMAD v3.7.1 (32-bit) using OpenSolverNomad v1.2.2 at “C:\Rossignolo\otimizacao de sistemas\OpenSolver271AdvancedWin\Solvers\win32\OpenSolverNomad.dll”
Your spreadsheet uses a function ‘transpose’ that don’t understand. Please try simplifying it. Andrew
is there any way to use the transpose function?
Not directly, but you can do the equivalent by inserting “=” formulae manually. Hope this helps. Andrew
Andrew,
I have a couple of more questions about OpenSolver 2.7.1:
(1) In Solver, you can specify non-contiguous cells for the variables by separating those cells with a comma in the Variable Cells box (for example, B2, D7). Am I correct that OpenSolver does not allow the use of a comma in the Variable Cells box and so all variable MUST BE CONTIGUOUS?
(2) Am I also correct that OpenSolver does not return the Answer Report that Solver generates and so with OpenSolver we do not get the values of the slack variables?
Thanks again for your time in answering these two questions.
Hold down control to select multiple cells for the variables. I have never found the answer report useful; you can calculate slack variables trivially on the sheet. Andrew
Perfect…thanks, Andrew!
I have two questions about using OpenSolver 2.7.1:
(1) When solving an LP from VBA, Solver returns a numerical value to indicate whether the LP is infeasible, optimal or unbounded. Does OpenSolver do the same? If so, how do I capture that numerical value? On a related note, is there some way in VBA to surpress the message box from OpenSolver when the LP is infeasible?
(2) When solving an integer program, is there any way to provide OpenSolver with an initial integer feasible solution whose objective function value COIN-OR can then use to improve computational efficiency? If so, how is this done?
Thanks for any insights you can provide.
Please see the VBA documentation at http://opensolver.org/opensolver-api-reference/
Unfortunately, we cannot pass initial solutions to CBC… this would be a good feature to add. Andrew
Thanks for your prompt response and congratulations on putting together a wonderful package…you have made a great contribution. I intend using OpenSolver in two of my courses this coming fall.
I hope the courses go well; all feedback is welcome. Andrew
Hi,
I am getting an error while running OpenSolver2.7.1 add-in on excel 2013 64-bit windows 7 64-bit
When I try solving the model I have build, a pop-up is coming with title ‘C:\WINDOWS\System32\cmd.exe’ which is not going.
When I try closing the pop-up manually it gave me the following error:
“OpenSolver 2.7.1 encountered an error:
The CBC solver did not complete, but aborted with the error code -1073741510.
The last log file can be viewed under the OpenSolver menu and may give you more information on what caused this error.
An error log with more details has been saved, which you can see by clicking ‘More Details’. If you continue to have trouble, please use the ‘Report Issue’ button or visit the OpenSolver website for assistance:”
Clicking on ‘More Details’ directed me to the error log, which gave me the following information:
“22 Dec 15 13:19:31 [OpenSolver.xlam] SolverCommon.RunLocalSolver: Line 0
22 Dec 15 13:19:31 [OpenSolver.xlam] SolverCommon.SolveModel: Line 0
22 Dec 15 13:19:31 [OpenSolver.xlam] OpenSolverAPI.RunOpenSolver: Line 0
Error -2147220501: The CBC solver did not complete, but aborted with the error code -1073741510.
The last log file can be viewed under the OpenSolver menu and may give you more information on what caused this error.
Solver: CBC
Version 2.7.1 (2015.06.28) running on 64-bit Windows 6.1 with VBA7 in 64-bit Excel 15.0”
Any direction will be useful, Thank You
Dear Andrew,
I would like to congratulate you and all the team for such an amazing project!
I am working on the development of production planning models using OpenSolver and, unfortunately, I am facing some issues when trying to solve them.
The first one is this:
This week I tried to solve one model and this message appeared at the screen:
OpenSolver 2.7.1 encountered an error:
Unable to delete the file: C:\Users\ube8957\AppData\Local\Temp\log1.tmp
What does it mean?
The second is this:
I have built some models and solved all of them with no problem. When the first problem came up I tried to solve these models I had already tested in order to see if the problem was only happening with the model with the first problem, but the solver could not find the optimal solution for most of them. Here is the message:
OpenSolver could not find an optimal solution, and reported:
Stopped on iteration limit.
It looks like I have to increase the iteration limit, but, when I used to solve these models there was no problem and the number of iterations was the same. So it does not make sense to me.
Even so, I increased the iterations limit and had no success.
I am pretty sure these models were feasible and that their structures are ok, because I have reviewed them a lot of times.
I would like to have some insights on how to solve them, please.
Thanks for your cooperation.
Antonio
Antonio: Sorry to hear of your problems.
The error “Unable to delete the file: C:\Users\ube8957\AppData\Local\Temp\log1.tmp” typically indicates taht Excel or OpenSolver crashed, leaving a file open; please (1) try quitting and restarting Excel (which is unlikley to work), and then (2) restarting your PC to close the file.
Re the iteration limit, newer versions of OpenSolver enforce this limit, which was not the case in earlier versions, and so increasing the limit is the right thing to do. However, you say this did not fix the problem; can you be more specific? Is the solver saying the model is infeasible? Are you sure the model is linear? Non-linear models can cause unpredictable results as we change OpenSolver (and ideally should always fail to solve, but we cannot guarantee this). Andrew
I see you are still using Sourceforge for distribution. Please search Sourceforge and malware.
We are aware of the potential issues with SourceForge, and we certainly do not agree with their tactics. However, as OpenSolver is an active project, we seem to be safe at the moment. Finding an alternative is on our ToDo list. Thanks, Andrew
Hi there. Is there sample code for running OpenSolver through VBA? I pieced together some code from the feedback thread, but am running into issues (particularly with SetDecisionVariables at the moment). Thanks.
Here’s what I have:
Dim TestSheet As Worksheet
Set TestSheet = Sheets(“Summary”)
OpenSolver.ResetModel Sheet:=TestSheet
‘Objective Definition
OpenSolver.SetObjectiveFunctionCell TestSheet.Cells(69, 38), Sheet:=TestSheet
OpenSolver.setobjectivesense MaximiseObjective, Sheet:=TestSheet
‘Variables Definition
OpenSolver.SetDecisionVariables (TestSheet.Range(Cells(7, 4), Cells(31, 37)))
‘Constraints Definition
OpenSolver.AddConstraint TestSheet.Range(Cells(32, 4), Cells(32, 37)), RelationLE, TestSheet.Range(Cells(38, 4), Cells(38, 37)), Sheet:=TestSheet
OpenSolver.AddConstraint TestSheet.Range(Cells(33, 4), Cells(33, 37)), RelationLE, TestSheet.Range(Cells(36, 4), Cells(36, 37)), Sheet:=TestSheet
OpenSolver.AddConstraint TestSheet.Range(Cells(7, 38), Cells(31, 38)), RelationGE, TestSheet.Range(Cells(134, 5), Cells(158, 5)), Sheet:=TestSheet
On Error GoTo errHandler_NoOpenSolver
Application.Run “OpenSolver.xlam!RunOpenSolver”
On Error GoTo errHandler
Exit Sub
errHandler:
Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
errHandler_NoOpenSolver:
Err.Clear
MsgBox “This workbook requires OpenSolver, a free Excel addin available at http://opensolver.org” + vbCrLf + vbCrLf + “Please install & then open OpenSolver, and then try again.”, vbOKOnly, “OpenSolver”
After a little more experimentation it isn’t ignoring the constraint. It isn’t recognizing differences in the numbers past 5 decimal places.
In other words it seems to consider 123.456789 = 123.456788
That is the designed behaviour… numeric algorithms have to operate within some precision tolerance. Andrew
I’m having issues with the following scenario, I’ll try to describe it generically.
Objective Cell is A1 which I am trying to maximize with about 20 constraints.
One of those constraints is that A1 must be <= to B1
However, the result is always the max value – it seem to ignore the A1 <= B1 constraint.
I am trying to automate the work on excel by running Opensolver from outside excel with python application OpenPyXL. I would like to know whether it is possible to kickstart opensolver the same way that RunOpenSolver does in VBA
You can generate a click on the Solve button; see SolverStudio.org for how to do this. Otherwise, I’m not sure; please let us know how you get on. Andrew
I see now that OpenPyXl appears to be only for reading/writing s/sheets. If Excel is not running, there is no way to run OpenSolver. If you want to use Python in Excel, I strongly suggest you look at SolverStudio http://solverstudio.org Cheers, Andrew
Dear Andrew,
can you suggest a way to link OpenSolver (instead of Frontline Solver) with SolverTable (http://kelley.iu.edu/albright/free_downloads.htm) to create parametric linear programs and solve them sequentially? Thanks!
You’d have to change the SolverTable’s VBA code. We have a long term plan to add something similar to OpenSolver, but have not yet done so. Andrew Mason
Hi!
My name is Aline and I am a student of Industrial Engineering.
I am using the OpenSolver 2.1 to do optimization tests for an academic project I have worked on applied to a Textile Industry.
I would like to know if there is any way that I can visualize the execution times for each optimization test performed. Is there any way that I could do that?
Thank you very much,
Aline.
Aline: I’d do the timing using VBA, or you might find some timing output in the CBC logs (but I’m not sure – you’d have to experiment with logging options). Good luck, Andrew
What is the maximum number of variables and the maximum number of constraints that open solver can solve. And is @RISK better than open solver? If yes then how? Please reply asap.
I just installed the OpenSolver Add-In with Excel 2013 Pro x64 and the model window looks like this: http://i.imgur.com/ImSImQG.png
If I click around where the text boxes should be, they appear. If I select too many constraints everything freezes up. If I try to click on the cell selection button to the right of the text boxes nothing happens.
Did I install it wrongly? I copied everything from the ZIP file to the addons directory and activated it in Excel. Are there necessary steps I missed?
Christoph
Christoph: Your “refedit” control in Excel is broken; please try repairing your Excel installation. Google for instructions for your version of Excel. Hope this helps, Andrew
Hi!
I am getting “Out of memory” error message while running a model. Could you please suggest on same?
Sorry it’s not working. Please tell us more… Windows or Mac? Which Solver are you using? When it is running out of memory… while building the model or while running the solver? How big is your model?
1. Am working on Version 2.7.1 and Windows Excel 2003. I am getting the error message while running the solver.
The data set is quite huge with around 875000 rows which also made excel processing quite slow. And then the constraints set requires formula calculation. Is using excel and open solver appropriate for such a variant?
2. Am adding another issue am facing for the same model ( but with a changed formula in cells referred in constraints) I ran with a smaller set of the data. Earlier the cell values were hard coded but I had to make it dynamic. So, I added cell value as =VLOOKUP($P$5&”|”&O6&”|”&$A$1,$A$5:$L$400,6,FALSE). But this gives an error on running the solver as mentioned below:
“Error -2147220502: The cell ‘P6’ in constraint ‘P6:X15 = P20:X29’ has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!). Please fix this and try ”
Please suggest. Many thanks in advance!
Hi Andrew,
1. Am working on Version 2.7.1 and Windows Excel 2003. I am getting the error message while running the solver.
The data set is quite huge with around 875000 rows which also made excel processing quite slow. And then the constraints set requires formula calculation. Is using excel and open solver appropriate for such a variant?
2. Am adding another issue am facing for the same model ( but with a changed formula in cells referred in constraints) I ran with a smaller set of the data. Earlier the cell values were hard coded but I had to make it dynamic. So, I added cell value as =VLOOKUP($P$5&”|”&O6&”|”&$A$1,$A$5:$L$400,6,FALSE). But this gives an error on running the solver as mentioned below:
“Error -2147220502: The cell ‘P6’ in constraint ‘P6:X15 = P20:X29’ has a divide by zero error or has a problem with the function used ( eg #DIV/0! or #VALUE!). Please fix this and try ”
Please suggest. Many thanks in advance!
Do you really mean 875,000 rows in Excel 2003? I’m impressed that Excel is managing with such a large spreadsheet. If that gives a model with 875,000 constraints, then I’d be very surprised if it worked in OpenSolver without running out of memory. Try SolverStudio instead (but even that will struggle I suspect). Regarding your model and the OpenSolver error, you may have a non-linear model, which CBC cannot solve, and is probably too big for NOMAD (the only non-linear solver you could consider). Sorry I cannot be of more help. Andrew
I am using Open solver to optimise the harvest schedule in a forest estate. To test various harvest options I need amend the constraints on a regular basis. However Open solver is cumbersome at present as you need to re-enter constraints after each run when some have been deleted/amended etc. It would be great to have the availability of a ‘check box’ to switch the various constraints on and off. thanks,
Peter. Nelson NZ
Peter: Great to hear that OpenSolver is helping NZ forestry. Yes, it is indeed cumbersome; adding checkboxes to turn on/off constraints is on our ToDo list (ideally in a way that lets users do this from the spreadsheet). In the meantime, you can adjust right hand sides to make constraints inactive; that’s what I do. Cheers, Andrew