Part V: Analyzing Data with Excel
764
l (^) Adjust the Maximum iterations setting on the Formulas tab of the Excel Options dialog
box (choose File ➪ Excel Options). Increasing the number of iterations (or calculations)
makes Excel try more possible solutions.
l Double-check your logic and make sure that the formula cell does, indeed, depend upon
the specified changing cell.
Note
Like all computer programs, Excel has limited precision. To demonstrate this limitation, enter =A1^2 into cell
A2. Then, use the Goal Seek dialog box to find the value in cell A1 (which is empty) that makes the formula
return 16. Excel comes up with a value of 4.00002269, which is close to the square root of 16, but certainly
not exact. You can adjust the precision on the Formulas tab of the Excel Options dialog box (make the
Maximum Change value smaller). n
Note
In some cases, multiple values of the input cell produce the same desired result. For example, the formula
=A1^2 returns 16 if cell A1 contains either –4 or +4. If you use goal seeking when multiple solutions are possi-
ble, Excel gives you the solution that is closest to the current value. n
Introducing Solver
The Excel Goal Seek feature is a useful tool, but it clearly has limitations. It can solve for only one
adjustable cell, and it returns only a single solution. Excel’s powerful Solver tool extends this con-
cept by enabling you to do the following:l (^) Specify multiple adjustable cells.
l Specify constraints on the values that the adjustable cells can have.
l (^) Generate a solution that maximizes or minimizes a particular worksheet cell.
l Generate multiple solutions to a problem.
Although goal seeking is a relatively simple operation, using Solver can be much more compli-
cated. In fact, Solver is probably one of the most difficult (and potentially frustrating) features in
Excel. I’m the first to admit that Solver isn’t for everyone. In fact, most Excel users have no use for
this feature. However, many users find that having this much power is worth spending the extra
time to learn about it.
New Feature
In Excel 2010, the Solver add-in received a much-needed facelift. In addition to the cosmetic improvements,
the product also performs better. n