Excel Solver

Excel Solver solves optimization problems from a spreadsheet. It is useful for small and medium LP models because the table can mirror the problem statement.

Spreadsheet Layout

A good layout has:

• one row or column for decision variables
• one row or column for objective coefficients
• one table for constraint coefficients
• one computed cell for the objective value
• one computed cell for each left-hand side constraint
• one cell for each right-hand side value

Objective Cell

If the decision variables are in cells $x_1,\ldots,x_n$ and coefficients are $c_1,\ldots,c_n$, compute:

In Excel this is usually a SUMPRODUCT formula.

Constraint Cells

For each constraint row, compute its left-hand side with SUMPRODUCT.

Example:

The cell for the left-hand side contains:

Solver then compares that cell to the right-hand side value $120$.

Solver Dialog

In Solver:

• Set Objective: choose the objective cell
• To: choose Max, Min, or Value Of
• By Changing Variable Cells: choose the decision variable cells
• Subject to the Constraints: add each constraint
• Solving Method: use Simplex LP for linear programming

Also add nonnegativity, either as explicit constraints or by selecting the nonnegative variable option when available.

Interpreting the Result

After solving, record:

• the value of every decision variable
• the optimal objective value
• which constraints are active
• which resources have slack
• whether Solver reports an optimal solution, infeasibility, or unboundedness

Exam Use

For solver-based exercises, the answer should not be only a screenshot or a number. State the variables, model, solution vector, and objective value.

See Also

• Solver Inputs
• Checking Solver Output
• LP Formulation

Exam checkpoint

For solver questions, the solver result is not enough. Also report the LP model, variable meanings, objective value, active constraints, and whether the result matches algebraic expectations.