Solver in Excel (Everything You Need to Know)

In the image below, we have enabled the Solver Add-in.

What is an Excel Solver?

The Excel Solver is an add-in tool in Microsoft Excel that helps you find an optimal solution for complex problems involving mathematical equations and constraints. It is primarily used for optimization and solving linear programming problems.

Definitions of terms:

Objective Cell: A single cell with a formula in it. The constraint cells’ limitations are applied to the decision-based formula in the cell. The objective cell’s value can be decreased, increased, or fixed at the provided threshold.

Variable Cells: These are made up of variable data that the Solver modifies to accomplish the goal.

Constraint Cells: These are the prerequisites that must be met.

How to Add Solver in Excel

Go to the File tab >> select Options.

From Add-ins >> select Excel Add-ins >> select Go.

Select Solver Add-in >> click OK.

Types of Solving Methods

1. GRG Nonlinear

Nonlinear problems can exhibit multiple feasible regions or a range of variable values that satisfy all the constraints.

2. Simplex LP

In a linear programming problem, the goal is to maximize or minimize a single objective while considering specific conditions.

The simplex LP and GRG nonlinear methods are employed to solve smooth problems.

3. Evolutionary

This approach is useful for resolving problems with jagged patterns, which involve functions that have breaks.

Further Examples of the Excel Solver

1. Finding Maximum (GRG Nonlinear)

Go to the Data tab >> Select Solver.

In the Set Objective box, select cell G10 >> click on Max.
Select cells F5:F9 in the By Changing Variables
Click on Add >> give the Constraints.
Click on Mark Unconstrained Variable Non-Negative >> select GRG Nonlinear.
Click on Solve.

In the Solver Result dialog box, select Answer >> click OK.

You can see the result in a different Excel sheet.

2. Finding Magic Square (GRG Nonlinear)

Go to the Data tab >> Select Solver.

Keep the Set Objective box empty >> click on Max.
Select cells C5:E7 in the By Changing Variables
Click on Add >> give the Constraints.
Click on Mark Unconstrained Variable Non-Negative >> select GRG Nonlinear.
Click on Solve.

You can see the rearranged magic square.

3. Interpreting Linear Programming (Simplex LP)

Select Solver from the Data

In the Set Objective box, select cell H10 >> click on Max.
Select cells C10:E10 in the By Changing Variables
Click on Add >> give the Constrain.
Click on Mark Unconstrained Variable Non-Negative >> select Simple LP.
Click on Solve.

Select Answer >> click OK.

You can see the answer report.

4. Coordinates and Mapping with Solver (Evolutionary Solver)

Enter the following formula in cell F5:

=SQRT((C5-OFFSET($C$13,E5,0))^2+(D5-OFFSET($D$13,E5,0))^2)

Enter the following formula in cell F14:

=SUM(F5:F10)

From the Data tab >> select Solver.
In the Set Objective box, select cell F14 >> click on Min.
Select cells in the By Changing Variables
Click on Add >> give the Constrain.
Click on Mark Unconstrained Variable Non-Negative >> select Evolutionary.
Click on Solve.