Adiabatic Mixing Temperature Calculator: Temperature-Dependent Cp(T) Energy Balance Guide

on

Excel Solver Optimization: GRG Nonlinear vs Evolutionary Algorithm Tuning Guide

on

This article explains how to choose between the GRG Nonlinear and Evolutionary solving methods in Excel Solver and how to tune their advanced options so that complex optimization models run faster, return more reliable solutions, and remain maintainable in enterprise spreadsheets.

1. Why Solver engine selection and tuning really matters

Excel Solver is often the hidden calculation engine behind pricing tools, production schedules, asset allocation sheets, and what-if simulations.

Yet many workbooks rely on the default settings without understanding how the GRG Nonlinear and Evolutionary methods behave or how their options influence solution quality and runtime.

Choosing the wrong engine or leaving parameters at unsuitable defaults can lead to any of the following outcomes.

  • Apparent convergence to a solution that is far from optimal.
  • Solver stopping too early with a “cannot improve the current solution” message.
  • Excessive run times that make a model unusable in day-to-day work.
  • Results that change unpredictably from run to run because of randomness in the search.

Understanding the differences between GRG Nonlinear and the Evolutionary Solver and how to tune their options is therefore a core skill for anyone building serious optimization models in Excel.

Note : Before experimenting with aggressive Solver options, always save the workbook and consider testing on a copy, because poorly tuned settings can cause Solver to run for a long time or make Excel unresponsive.

2. Overview of Excel Solver solving methods

Excel Solver provides three main solving engines.

  • Simplex LP for linear optimization problems.
  • GRG Nonlinear for smooth nonlinear problems.
  • Evolutionary for non-smooth, discontinuous, or highly combinatorial problems.

This article focuses on the comparison between GRG Nonlinear and the Evolutionary Solver, because they are frequently used on similar models but behave very differently.

2.1 GRG Nonlinear in plain language

GRG stands for Generalized Reduced Gradient.

Conceptually, GRG Nonlinear does the following.

  • Starts from an initial guess for the decision variables.
  • Computes the local gradient (slope) of the objective with respect to each variable.
  • Moves in a direction determined by these gradients while trying to satisfy constraints.
  • Stops when the gradients are very small and constraint violations are within tolerances.

This method is fast and efficient when the model is smooth and reasonably well scaled.

However, it is a local search method, so it may converge to a local optimum that depends on the starting values.

2.2 Evolutionary Solver in plain language

The Evolutionary Solver uses a genetic algorithm style approach.

Instead of following gradients from a single starting point, it maintains a population of candidate solutions.

  • Generates an initial population of solutions within the variable bounds.
  • Evaluates the objective value for each candidate.
  • Combines and mutates candidates to create new ones, favoring better performers.
  • Iteratively evolves the population until improvement stalls or another stopping condition is met.

Because it does not rely on gradients, the Evolutionary method can handle non-smooth objectives, discontinuities, and models with large numbers of binary or integer decision variables.

In exchange, it is usually slower than GRG Nonlinear and may require more careful tuning of parameters such as population size and mutation rate.

3. GRG Nonlinear vs Evolutionary: when to use which

The table below summarizes typical scenarios and the engine that is usually more appropriate.

Model characteristic GRG Nonlinear Evolutionary Solver
Smooth, differentiable formulas (no IF-based kinks) Generally preferred, fast and accurate. Not necessary but can still work, typically slower.
Many IF, MIN, MAX, LOOKUP, CHOOSE creating kinks or discontinuities May struggle or converge to poor local optima. Preferred, robust to non-smooth surfaces.
Mostly continuous variables Well suited. Works, but often slower than GRG.
Heavy use of binary or integer variables (0/1 decisions, integer counts) Can handle some, but performance may degrade. Typically better choice for complex combinatorial structures.
Objective with many local optima Local search, may get stuck near the starting point. Population-based search can explore more of the space.
Need reproducible, deterministic results Deterministic for given starting point and options. Can be deterministic if the Random Seed is fixed.
Need fast answers during interactive modeling Generally faster. May be too slow for large models without tuning.
Note : If your model is almost linear or purely linear but you have selected GRG Nonlinear or Evolutionary, consider switching to Simplex LP, which is usually much faster and more reliable for true linear programs.

4. Key GRG Nonlinear options and how they affect tuning

The GRG Nonlinear options in Solver control how aggressively the algorithm searches and how accurately it enforces the optimality conditions.

The most important ones in practical modeling are listed below.

4.1 Convergence

Convergence is a tolerance on how much the objective function must improve from iteration to iteration for Solver to continue searching.

  • Smaller values (for example 0.0001) require more improvement and thus enforce stricter convergence.
  • Larger values allow Solver to stop sooner but may accept a solution that is further from the true optimum.

For financial or engineering models where objective values can be large, it is often helpful to scale the model so that the objective is in a reasonable numerical range (for example 10–10,000) and then use a moderate convergence tolerance.

4.2 Precision and constraint satisfaction

Precision controls how tightly Solver tries to satisfy constraints.

  • Lower precision values demand tighter constraint satisfaction and may increase run time.
  • Higher precision values allow slightly larger violations but run faster.

In many business models a small constraint violation (for example, a capacity of 400.0001 instead of 400) is acceptable, so an overly strict precision setting may only slow down the optimization without improving decisions.

4.3 Derivatives: Forward vs Central

GRG Nonlinear estimates partial derivatives using finite differences.

  • Forward differencing is faster but less accurate.
  • Central differencing is more accurate but requires roughly twice as many worksheet recalculations.

Use central derivatives when.

  • The model is smooth and differentiable.
  • You observe oscillations or poor convergence with forward derivatives.
  • Maximum accuracy is more important than speed.

4.4 Multi-start and global search (where available)

Recent versions of Solver can offer a multi-start option with GRG Nonlinear.

With multi-start enabled, Solver runs multiple GRG searches starting from different randomly chosen points within the variable bounds, then returns the best result found.

This improves the chances of escaping poor local optima at the cost of additional run time.

4.5 Automatic scaling

Use Automatic Scaling is a general option that strongly affects GRG performance.

  • If decision variables and constraints have very different magnitudes (for example, some in the range of 0.001 and others in the range of 1,000,000), GRG can struggle.
  • Automatic scaling attempts to normalize the problem so that all variables have similar impact on the search.

Unless you have manually scaled your model, enabling automatic scaling is usually beneficial.

5. Practical GRG Nonlinear tuning workflow

The following step-by-step process helps tune GRG on real models.

5.1 Step 1: Clean up the model structure

  • Remove unnecessary IF, MIN, MAX, and other non-smooth constructs from the region near the optimum if possible.
  • Separate out intermediate calculations into clearly labeled cells to simplify debugging.
  • Ensure that formulas are correct for a wide range of plausible variable values.

5.2 Step 2: Choose good starting values

  • Use business logic to set initial guesses close to feasible and realistic values.
  • Avoid starting from extreme or obviously infeasible values when possible.
  • If Solver converges to poor solutions, try alternative starting points.
Note : GRG Nonlinear is sensitive to starting values because it performs local search. Testing a few reasonable starting points is often faster than immediately switching to the Evolutionary method.

5.3 Step 3: Adjust convergence and precision

  • Begin with moderate settings such as convergence at 0.0001 and precision near the default.
  • If Solver stops quickly with a poor solution, tighten convergence or precision slightly.
  • If Solver runs very slowly with little improvement, relax convergence or precision or rescale the model.

5.4 Step 4: Switch to central derivatives if needed

  • If GRG reports that it cannot find a feasible solution even though one exists, or if the solution seems unstable when you recalc the sheet, consider using central derivatives.
  • Monitor run time. If the model becomes too slow, consider simplifying formulas or reducing model size.

5.5 Step 5: Use multi-start for non-convex models

  • For models with multiple local optima (for example, models involving trigonometric functions or piecewise penalties), enable multi-start if available.
  • Limit the time budget so that Solver does not run indefinitely. Multi-start can be computationally expensive.

6. Evolutionary Solver options and their impact

The Evolutionary method introduces additional options specifically designed for population-based search.

6.1 Population size

Population Size is the number of candidate solutions maintained at each generation.

  • Very small populations may converge quickly but risk getting trapped in poor regions of the search space.
  • Larger populations explore more broadly but increase the number of objective evaluations and run time.
  • A common practical rule is to start with a population proportional to the number of decision variables and then adjust based on observed performance.

6.2 Mutation rate

Mutation Rate controls how frequently variables in candidate solutions are randomly changed.

  • Low mutation rates preserve good structures but risk premature convergence.
  • High mutation rates increase diversity and exploration but can slow convergence and destabilize promising solutions.
  • In practice, moderate mutation rates combined with appropriate population sizes often provide a good balance.

6.3 Convergence for Evolutionary Solver

As with GRG, the Evolutionary method uses a Convergence parameter describing how much the best objective value must improve to consider the search still productive.

  • Strict convergence criteria force the algorithm to keep searching for small improvements, potentially increasing run time.
  • Relaxed criteria allow Solver to stop earlier, which is useful when approximate solutions are acceptable.

6.4 Random seed and reproducibility

Random Seed determines the random choices underlying initial population generation and mutation.

  • If the random seed is fixed, repeated Solver runs with the same options and model will produce identical results.
  • If the random seed is left blank, each run will generate different random sequences, which can sometimes yield better solutions but complicate reproducibility.

6.5 Maximum time without improvement

Maximum Time without Improvement caps how long Evolutionary Solver continues without meaningful improvement in the best objective value.

  • If set too low, Solver may stop before it has had a chance to explore the search space effectively.
  • If set too high on a large model, Solver may run excessively long with little benefit.

6.6 Require bounds on variables

The Require bounds on variables option instructs Solver to run only when every decision variable has both lower and upper bounds defined via constraints.

  • Bounds dramatically improve Evolutionary performance because they constrain the search region.
  • Without bounds, the search space can be effectively unbounded, making it much harder to find good solutions.
Note : When using the Evolutionary method, always define realistic lower and upper bounds on every decision variable. This single step often has more impact on performance than fine tuning population size or mutation rate.

7. Practical Evolutionary Solver tuning workflow

Compared with GRG, tuning the Evolutionary method is more about managing exploration versus exploitation and controlling run time.

7.1 Step 1: Define tight, realistic bounds

  • For each decision variable, determine minimum and maximum values that are truly plausible in the problem context.
  • Express these as Solver constraints rather than leaving variables unconstrained.
  • Avoid bounds that are artificially wide “just to be safe”, as they inflate the search space and slow convergence.

7.2 Step 2: Start with default settings and observe behavior

  • Run Solver with default population size, mutation rate, and convergence to establish a baseline.
  • Record objective value, run time, and whether Solver reports any warnings.
  • Repeat the run a few times with different random seeds to gauge variability.

7.3 Step 3: Adjust population size

  • If the model has many variables and Solver appears to stall in poor regions, gradually increase population size.
  • If each run takes too long with little improvement between generations, consider reducing population size.
  • Balance population size against available run time and business needs for responsiveness.

7.4 Step 4: Tune mutation rate

  • If different runs converge to similar but clearly suboptimal solutions, raise mutation rate slightly to encourage more exploration.
  • If the best solution value oscillates widely or never stabilizes, lower the mutation rate to preserve good structures.
  • Adjust in small increments and change only one parameter at a time when diagnosing behavior.

7.5 Step 5: Manage stopping conditions

  • Use the Maximum Time without Improvement option to prevent Solver from running indefinitely when progress has effectively stopped.
  • Combine this with a reasonable overall time budget so the model remains usable during everyday work.
Note : For strategic planning models where obtaining a slightly better solution justifies more time, you can allow longer Evolutionary runs with larger populations. For operational dashboards used many times per day, prioritize faster convergence over marginal improvements in the objective.

8. Combined strategy: GRG first, Evolutionary second

In many real-world situations, neither GRG Nonlinear nor Evolutionary Solver alone provides the ideal balance of speed and robustness.

A practical hybrid strategy is often effective.

  1. Run GRG Nonlinear from a reasonable starting point to obtain a quick, locally optimal solution.
  2. Use the GRG solution as a seed or reference for an Evolutionary run, perhaps by narrowing bounds around the GRG solution.
  3. Let Evolutionary explore the neighborhood and search for better points, especially for models with suspected multiple local optima.

This two-stage approach combines the speed of GRG for coarse positioning with the robustness of Evolutionary for global exploration.

9. Example: tuning Solver for a capacity allocation model

Consider a simplified capacity allocation model where a company allocates production across several plants to minimize total cost subject to capacity and demand constraints.

  • The cost function includes volume discounts, creating piecewise structures.
  • Some plants must either be fully open or closed, represented by binary variables.
  • There are both continuous and integer variables.

9.1 Attempt with GRG Nonlinear

First, the modeler tries GRG Nonlinear.

  • Starting values are set near current production levels.
  • Convergence and precision remain at default values.
  • Automatic scaling is enabled.

Solver finds a solution quickly, but subsequent analysis shows that small perturbations in starting values yield different solutions and objective values, suggesting that the surface has multiple local optima driven by the piecewise discounts and binary decisions.

9.2 Switching to Evolutionary

The modeler then moves to the Evolutionary method.

  • Bounds on production quantities are set tightly around realistic capacity ranges.
  • Binary variables are constrained to 0–1 with integer constraints.
  • Population size is increased moderately to improve exploration.
  • Mutation rate is set to a moderate value, then tuned based on observed convergence behavior.

After a few tuning iterations, Evolutionary consistently finds solutions with lower total cost than any GRG run, albeit with longer run times.

The final configuration uses a hybrid workflow.

  • GRG is used in daily planning workbooks where speed is critical.
  • Evolutionary is used in weekly strategic reviews where a better global solution is worth the additional compute time.

10. Automating Solver tuning with VBA

For complex models that must be solved repeatedly, you can script Solver options using VBA to standardize tuning across workbooks.

The following example outlines how you might configure GRG Nonlinear with specific options.

Sub RunGRGModel() ' Reset Solver to a clean state SolverReset ' Define the optimization problem SolverOk _ SetCell:="$F$2", _ ' Objective cell MaxMinVal:=2, _ ' 1 = Max, 2 = Min, 3 = ValueOf ByChange:="$B$2:$D$2" ' Decision variable range ' Configure GRG Nonlinear options SolverOptions _ AssumeLinear:=False, _ ' Nonlinear model Precision:=0.000001, _ ' Tight constraint satisfaction Convergence:=0.0001, _ ' Convergence tolerance Derivatives:=2, _ ' 1 = Forward, 2 = Central Scaling:=True ' Use automatic scaling ' Solve without showing the Solver dialog SolverSolve UserFinish:=True End Sub

Similarly, you can configure the Evolutionary method.

Sub RunEvolutionaryModel() SolverReset SolverOk _ SetCell:="$G$2", _ MaxMinVal:=2, _ ByChange:="$C$2:$H$2" ' Evolutionary tuning SolverOptions _ AssumeLinear:=False, _ PopulationSize:=200, _ ' Larger population for exploration MutationRate:=0.07, _ ' Moderate mutation rate Convergence:=0.0001, _ RandomSeed:=12345 ' Fixed seed for reproducibility SolverSolve UserFinish:=True End Sub

Centralizing Solver configuration in code helps ensure that all analysts in a team use consistent, validated tuning settings, which improves reproducibility and governance for critical spreadsheet models.

FAQ

Should I always switch to Evolutionary when GRG fails to converge?

No.

When GRG fails, first check whether the model is smooth, well scaled, and has reasonable starting values.

Fixing modeling issues or adjusting convergence, precision, and derivatives often restores GRG performance.

Move to Evolutionary when the model is inherently non-smooth, highly combinatorial, or clearly exhibits multiple local optima that GRG cannot escape.

How do I know if my model is too non-smooth for GRG Nonlinear?

Indicators include heavy use of IF, MIN, MAX, LOOKUP, CHOOSE, or other functions that create kinks or discontinuities near the optimum.

If small changes in decision variables cause abrupt jumps in the objective or constraints, GRG may struggle.

In such cases, consider refactoring the model to reduce non-smoothness or use the Evolutionary method with well-defined variable bounds.

What is a reasonable starting point for Evolutionary population size?

A practical approach is to start with a population proportional to the number of decision variables, then adjust based on observed performance.

If the search stagnates early, increase population size to promote exploration.

If the model is small and runs are slow, reduce population size and possibly the number of generations or time budget.

How can I make Evolutionary Solver results reproducible?

Specify a fixed Random Seed value in the Solver options or via SolverOptions in VBA.

With a fixed seed, the same model, options, and starting conditions will produce identical results across runs, which is important for auditability and validation.

Is it safe to tighten precision and convergence as much as possible?

Not always.

Overly strict precision and convergence settings can significantly increase run time without improving decisions in a meaningful way, especially when model data and parameters are uncertain.

Choose tolerances that are consistent with the level of accuracy actually needed for business decisions or engineering constraints.

추천·관련글