Is it better to call Solver twice or doubling the iterations?

by

Summary

The question revolves around the optimization technique used in Excel’s Solver tool, specifically whether it’s more effective to double the max iterations or execute the Solver twice with a lower max iteration limit, starting from the result of the previous calculation. The author observed that executing Solver twice with reduced iterations led to consistent convergence, whereas a single execution with higher iterations sometimes failed to converge.

Root Cause

The root cause of this behavior lies in the numerical methods used by the Solver tool. Key factors include:

  • Initial guess: The starting point for the optimization algorithm
  • Iteration limit: The maximum number of iterations allowed
  • Convergence criteria: The conditions under which the algorithm considers the solution optimal
  • Algorithmic complexity: The inherent complexity of the problem being solved

Why This Happens in Real Systems

In real-world systems, non-linear relationships and local optima can cause optimization algorithms to fail to converge or get stuck in suboptimal solutions. The repeated execution of the Solver with a lower iteration limit can help escape local optima and refine the solution.

Real-World Impact

The impact of this issue includes:

  • Inconsistent results: Failure to converge or inconsistent solutions
  • Increased computational cost: Higher iteration limits or repeated executions can increase computation time
  • Decreased reliability: Dependence on manual intervention or trial-and-error approaches

Example or Code

Sub ExecuteSolverTwice()
    ' Set up the Solver parameters
    SolverReset
    SolverOptions MaxTime:=100, Iterations:=100, Recalc:=1, StepThru:=False

    ' Execute Solver twice with reduced iterations
    SolverOk SetCell:="$B$2", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$2"
    SolverSolve (True)
    SolverSolve (True)
End Sub

How Senior Engineers Fix It

Senior engineers address this issue by:

  • Understanding the optimization algorithm and its limitations
  • Analyzing the problem structure to identify potential local optima or non-linear relationships
  • Implementing robust optimization techniques, such as multi-start methods or hybrid algorithms
  • Monitoring and adjusting the optimization process to ensure consistent convergence

Why Juniors Miss It

Junior engineers may overlook this issue due to:

  • Lack of experience with optimization algorithms and their limitations
  • Insufficient understanding of the problem structure and its potential pitfalls
  • Overreliance on default settings or trial-and-error approaches
  • Inadequate testing and validation of the optimization process