CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1886

Multiple objectives in Excel solver

I have a macro that runs through multiple sets of permutations using Excel's solver. Each macro runs the solver 3 times, using Simplex followed by 2 x Evolutionary. It seems to work reasonably well.

However, I noticed that the solution keeps changing, due to one of the variables only being constrained by a maximum value. As such, I need to make the variable a max/min objective.

I know that Excel's solver only allows for one max/min objective. Is there any way that I can set a second max/min objective?

The code I have is as follows:

a = Range("Q1")

    SolverReset
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$O$1", Relation:=1, FormulaText:="80"
    SolverAdd CellRef:="$M$1", Relation:=2, FormulaText:="8"
    SolverAdd CellRef:="$P$1", Relation:=3, FormulaText:="5"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Simplex LP"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=4, FormulaText:="integer"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Simplex LP"
    SolverSolve ShowRef = 0

    SolverReset
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverAdd CellRef:="$O$1", Relation:=1, FormulaText:="80"
    SolverAdd CellRef:="$M$1", Relation:=2, FormulaText:="8"
    SolverAdd CellRef:="$P$1", Relation:=3, FormulaText:="5"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=4, FormulaText:="integer"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverSolve ShowRef = 0

    SolverReset
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverAdd CellRef:="$O$1", Relation:=1, FormulaText:="80"
    SolverAdd CellRef:="$M$1", Relation:=2, FormulaText:="8"
    SolverAdd CellRef:="$P$1", Relation:=3, FormulaText:="5"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=4, FormulaText:="integer"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$M$2:$M" & a, Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$N$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$M$2:$M" & a, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverSolve ShowRef = 0

Upvotes: 1

Views: 14618

Answers (1)

Algau
Algau

Reputation: 50

You shall see this as a sequential process.

  1. Obtain the initial optimal solution.
  2. Add the optimal value to the system of constraints.
  3. Solve again the whole problem, but with your new objective wich would be to maximize (or minimize) your variable of interest.

So your first objective will be fulfilled, and by adding it's optimal value to the system of constraints it won't be possible to affect it. And then, as long as it doesn't interfere with the previous objective, your second objective will also be fulfilled.

In fact, doing so you could add as many objectives as you want and they would be solve with a lexicographic order (higher priority given to the previous objective).

Upvotes: 3

Related Questions