EXCEL SOLVER TUTORIAL
What Can the Solver Do?
The Solver can be used for both
equationsolving
(often called goalseeking or backsolving) and
constrained optimization
(using linear programming, nonlinear programming, and integer
programming methods).
EquationSolving
. In the normal "whatif" operation of a spreadsheet, you enter or change
input
values, and the spreadsheet calculates the
output
values of various formulas which depend on
your inputs. The Solver can be thought of as performing "whatif in reverse": You specify the
output
values, or ranges of values that you would like certain formulas to have, and the Solver
determines the
input
values which calculate the output values you want.
Constrained Optimization.
You can also ask the Solver to find input values which satisfy of set
of simultaneous equations and
inequalities
(involving <= or >=). When you do this, there is
usually more than one satisfactory set of input values. So the Solver can find the "best" set of
input values which maximizes or minimizes some other calculated formula that you specify. This
is called
constrained optimization
; the equations or inequalities (you can use both) are called
constraints
.
The Solver can also simply maximize or minimize a formula without any constraints. For
example, if you ask the Solver to find A1 such that (A1/21)^2 is minimized, the Solver will find
A1=2, where the formula has the value 0.
The input values to be found by the Solver, which are called adjustable cells or
decision
variables
, are simply cells containing numbers on your spreadsheet. The constraints each consist
of a cell which calculates a formula, a relation (=, <= or >=), and another cell which calculates a
formula. And the function to be maximized or minimized, called the
objective function
, is just
another cell containing a formula.
We have a problem:
Define:
Objective: What is the goal of the problem?
Decision variables: What are we trying to decide?
Constraints: What are the limitations in the problem? Upper bounds, lower bound?
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '08
 Perevalov
 Optimization, crude oil, input values

Click to edit the document details