|
Dear OR-Champs! I have an optimization (IP) model where I am trying to define the decision variables as integers. Unfortunately Excel does not have an option to define the variables as integers. Please can anyone help? Happy new year to all of you!!! Thanks! |
|
Within Solver, define an integrality constraint by choosing the cells and then, on the dropdown menu that begins "<=" choose "int" for integer or "bin" for binary.
Thank you Mr. Trick. Using excel after long-time for OR. (kind of figured it out after little while exploring all the options)
(Jan 07 at 05:22)
Pavan
|
|
To add to Mike's answer, it's also important to take into account the "Tolerance" parameter that can be adjusted by clicking the "Options" button in the Solver setup window. I believe that the default value is 5%, which means that Excel will stop looking for an optimal solution when the integrality gap is 5%. If you want the guaranteed true optimal solution, you need to set tolerance to 0. |
Mr. Trick, thank you for answering earlier. I also have another question.
While modeling in MS-Excel, its quite easy to model the allocation problem for single product. How to model it when the problem has multiple products? (apart from creating 5 different separate models)
Say in AMPL, I can easily define the constraint "subject to {p in products}: ...XXX..." such that for each product the parameters would choose the respective values. How to define this Excel?
As always, sincerely appreciate your responses.
Afaik, Excel has no support for set algebraic expressions. Yet, to express (and pool) constraints in a more efficient way you can leverage Excel's capability of column/row comparisons. - Two tutorials on Excel modeling practices that might come handy:
Thanks Fbahr, I'll have a look.