CODEWITHSUNDEEP
exceloptimizationsolvervba
Jonhtra
Jonhtra

Reputation: 947

Generating multiple optimal solutions using Excel solver

Is there a way to get all optimal solutions when you are solving some problem with Excel Solver (Simplex LP method)?

If not, what is the best way/add-in to Excel to solve it and convert existing VBA code to use this new way?

Upvotes: 3

Views: 19426

Answers (2)

Jonhtra
Jonhtra

Reputation: 947

Actually, I have found a way to do this with Excel solver, although it is not optimal in sense of time consumption but that is not issue for me.

If you can assign unique id for each possible solution on some way, which is true in my case, then for each solution you find you can check if there is some solution with same value with different id on following way :

  1. Find first optimal solution and save solution id and result. I will call this origID, origRes

  2. Check if there is some solution with id < origID and res = origRes

  3. If yes, then consider newId as initial id and continue with step 2 until you can't find solutions which satisfied criteria

  4. After that, do the same thing with condition id > origID and res = origRes

  5. After you make sure you found all solutions with optimal solution origRes, then we can go and find solution which is not optimal as origRes. I did it on a way to add condition that new solution needs to be <= (origRes - 0.01) because I know that all solutions will be with 2 decimal places.

  6. Go to step 2 again

I know this is not the best way but I usually do not need more than 100 solutions and currently I can get it in 2 mins which is acceptable for me.

Upvotes: 4

Erwin Kalvelagen
Erwin Kalvelagen

Reputation: 16724

Although this looks easy, it actually is not such an easy question. Even the definition of "all possible optimal solutions" is not clear. There may by infinitely many of them. Asking for "all basic feasible solutions" (i.e. corner points) sounds better. To my knowledge there are no solvers providing this. I also do not know of a really simple technique to enumerate all optimal bases.

One interesting approach is to use a MIP formulation to enumerate all optimal bases:

Sangbum Lee, Chan Phalakornkule, Michael M. Domach, Ignacio E. Grossmann, "Recursive MILP model for finding all the alternate optima in LP models for metabolic networks," Computers and Chemical Engineering 24 (2000) 711-716. (link)

Upvotes: 0

Related Questions