2

I have the following problem: I need to minimize a cell by varying another cell value, which need to be an integer. My Excel Solver works ok sometimes, and other times it does not solve it. I noticed that it depends on the initial conditions, so according to the initial variable cell value, the solver might find a solution which is not optimum.

Any idea about it?

attached you find the piece of VBA code which runs the solver.

 Sub optimizer1()

 optimizer1 Macro



    SolverReset                                                                             'resetting solver
    SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
        1, EngineDesc:="GRG Nonlinear"                                                      'setting up target and input cell
    SolverAdd CellRef:="$Q$4", Relation:=4, FormulaText:="integer"                          'The value of variable cell must be integer
    'SolverAdd CellRef:="$U$4", Relation:=1, FormulaText:="$L$4"                            'setting up the range (these are the boundary conditions)
    'SolverAdd CellRef:="$U$4", Relation:=3, FormulaText:="$K$4"
    'SolverAdd CellRef:="$AA$4", Relation:=1, FormulaText:="$L$4"
    'SolverAdd CellRef:="$AA$4", Relation:=3, FormulaText:="$K$4" '(some Boundary conditions have been disabled, please neglect them for the purpose of the question)
    SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
        1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$Q$4", Relation:=3, FormulaText:="$O$4"
    SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
        1, EngineDesc:="GRG Nonlinear"                                                      ' Another boundary condition
    SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
        1, EngineDesc:="GRG Nonlinear"
    SolverSolve UserFinish:=True
End Sub

Thank you!

  • Did you investigate the cases in which it finds a non-optimal solution. Is it a local minimum/maximum. i.e. the values left or right of it result in a worse result? – Luuklag Feb 03 '17 at 15:16
  • Try using the "multistart" option for GRG nonlinear. See [this article](http://www.solver.com/excel-solver-global-optimization) – OldUgly Feb 03 '17 at 17:29

0 Answers0