Calculation Issues

Why Excel Solver Produces Different Results Each Time and How to Fix It

Steven Parker Oct 23, 2026 869 views

Question details

User experiences inconsistent outcomes when running Excel Solver, receiving different results on subsequent calculations.

Why Excel Solver Produces Different Results Each Time

Product: Excel
Device & OS: not provided
Scenario: Optimizing models using the Evolutionary solving method or utilizing IF formulas that depend on decision variables.
Observed behavior: The Solver generates random or varying solutions each time it is executed, failing to consistently land on the same local or global optimum.

Before you start

Before adjusting solver settings, save a copy of your workbook to preserve your original data and ensure you have a clear understanding of the objective cell and mathematical constraints you intend to apply.

Solution 1Recommended

Adjust Evolutionary Solver Parameters

Tweak the convergence tolerance, mutation rate, and population size to stabilize the randomized nature of the Evolutionary solving method.

The Evolutionary method inherently uses randomized candidate solutions to explore a wide range of data. Because of this random generation, it may return different results each time you run it. Adjusting the internal parameters can help force the engine to calculate more thoroughly before stopping.

Open Solver Options

Navigate to the Data tab, click on Solver, and once the Solver Parameters dialog opens, click on the Options button.

Modify Evolutionary Settings

Switch to the Evolutionary tab within the Options dialog. Increase the Population Size and adjust the Mutation Rate to ensure a broader and more rigorous search.

Tighten Convergence

Lower the Convergence tolerance value. A smaller number forces the Solver to keep searching for a better result instead of stopping early.

Run and Compare

Click OK, run the Solver multiple times, and compare the objective values to identify the most consistent and optimal result.

Processing Time: Increasing population size and tightening convergence will make the Solver take longer to calculate. Be prepared for longer processing times on complex workbooks.

Solution 2

Remove Discontinuous IF Formulas

Replace IF functions that depend on decision variables, as they create mathematical discontinuities that confuse optimization algorithms.

Solution 3

Use a VBA Macro for Systematic Testing

Build a custom macro to systematically test candidate points and find a reproducible breakpoint instead of relying on random evolutionary generation.

Achieve Consistent Data Analysis with WPS Spreadsheet

If you frequently work with complex mathematical models, WPS Spreadsheet offers robust What-If Analysis tools. Enjoy a familiar interface that handles complex constraints reliably without the heavy processing overhead.

1. Download and Install: Download WPS Office Free and install it on your computer.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook.
3. Access What-If Analysis: Navigate to the Data tab and click on What-If Analysis to access Goal Seek.
4. Compute Results: Input your target parameters, changing cells, and constraints to get fast, accurate, and consistent calculations.

100% compatible with Microsoft Excel (.xlsx) formatsLightweight and fast calculation engine for large datasetsBuilt-in Goal Seek and advanced data analysis featuresCompletely free to use for everyday spreadsheet optimization

microsoft office alternative - wps office

Frequently Asked Questions

Does the GRG Nonlinear method also produce different results each time?

No. Unlike the Evolutionary method, which uses random number generation to find candidates, the GRG Nonlinear method relies on gradient-based logic. It usually produces the same result consistently, provided the starting values remain exactly the same.

What is a good population size for the Evolutionary Solver?

A larger population size (e.g., 100 or 200) increases the chance of finding a global optimum by evaluating more random points, but it significantly slows down calculation times. Adjust it based on the complexity of your constraints and your computer's processing power.

How do IF formulas affect the Excel Solver?

IF formulas create sudden mathematical jumps or discontinuities. Standard gradient-solving algorithms (like GRG Nonlinear) fail because they cannot calculate derivatives across a jump. Evolutionary solvers bypass this by using random sampling, but the discontinuities make it difficult to guarantee a perfect global optimum.

Can I save my Solver parameters to reuse them later?

Yes. In the Solver Parameters dialog box, you can click 'Load/Save' to store your objective, constraints, and specific engine options (like mutation rate) into a range of cells on your worksheet. You can load them back anytime to ensure you are testing the exact same setup.

Excel & Data Problems