Find, Install and Use the Solver in Excel

Find and Install

Go to the File tab.

Select Options.

In Excel Options, select Add-Ins.
Select Manage.
Choose Excel Add-Ins and click Go.

ICheck Solver Add-In and click OK.

Alternatively:

Go to the Developer tab.
Click Excel Add-ins in Add-ins.

Check Solver Add-In and click OK.

The add-ins will be visible on the ribbon.

Go to the Data tab.

You will find Solver in Analyze.

Solver Parameters

The Excel Solver determines the best solution to the formula in the target cell.
The Solver Parameters are:

Objective Cell: The objective cell value can be decreased, increased, or fixed.
Variable Cells: Contain variable data that the Solver modifies to accomplish the goal.
Constraint Cells: The prerequisites that must be met.

How to Use Solver in Excel -2 Examples

Example 1 – Using the Solver to Interpret Linear Programming in Excel

You have a decision model: each LED TV gives a Profit of $100, Costs $450, needs a Space of 0.6 units. The same parameters are defined for AC and Refrigerator. The company has a Capital of $60000 and 100 units of space available.

Steps:

Select G7 and enter the following formula.

=SUMPRODUCT(C7:E7,C10:E10)

C7:E7 represents the Cost of the three products and C10:E10 the Order Amount. The SUMPRODUCT function takes the two arrays as an argument, multiplies the values of the arrays, and returns the sum.

Press ENTER.

Select G8 and enter the following formula.

=SUMPRODUCT(C8:E8,C10:E10)

Press ENTER.

Select H10 and enter the following formula.

=SUMPRODUCT(C5:E5,C10:E10)

It calculates the Total Profit by multiplying the Profit/Unit by the Order Amount.

Press ENTER.

To check the formulas, enter data in C10:E10:

The Total Profit amount isn’t optimized. Use the Solver:

Go to the Data tab.
Select Solver in Analyze.

In Solver Parameters, enter the cell reference H10 in Set Objective.
Select Max in To.
Enter the cell reference C10:E10 in By Changing Variable Cells.
Click Add.