Page 80 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 66

EXCEL DATA ANALYSIS

USING SOLVER TO PRODUCE SPECIFIC VALUES

f you want to create a formula in your worksheet that To produce the desired results for your function, Excel
produces a specific result, you can use the Solver option changes the values of the cells referenced by your
I to determine the necessary argument values. This is a arguments; therefore, you must specify which cells Excel
handy tool for the analyst who wants to determine how can modify. In the business loan example, if you want to
much of a product a division must sell to make a specific change both the mortgage amount and the interest rates,
profit, or, using the PMT function, how much money to you specify these cell references, and Excel calculates a
borrow and make a monthly payment of $1500 on a solution by modifying them. When the Solver finds a
business loan. solution, you can either apply the new cell values or return
to the original values. Note that after you apply the Keep
In the Solver Parameters dialog box, you specify what cell, Solver Solution option, you cannot undo the changes to
always the cell containing the formula, must produce the the cells.
expected result. You must also define what results your
formula must produce. You can have Excel solve the The Solver option is an Excel Add-in, which means that by
formula until it matches, is larger than, or is less than the default, the option is not loaded. If you do not find it in the
specific end result value. For example, you can select the Tools menu, you can load it in the Add-ins dialog box. See
Value of option to find the values that return –1550 from Chapter 11 for more information on loading Add-ins in
the PMT function, which returns a negative value to Excel.
indicate a payment amount.

USING SOLVER TO PRODUCE SPECIFIC VALUES

$B$4

⁄ Click the cell containing ¤ Click Tools ➪ Solver. ■ The Set Target Cell field ˇ Type the range of cells to
the formula. contains the formula cell change.
■ The Solver Parameters reference.
dialog box displays. ■ You can add constraints by
‹ Click an end result clicking Add.
option.
Á Click Solve.
› Type the desired formula
value in this field.
66