Microsoft Office Tutorials and References
In Depth Information
Using the Solver
tab, clicking Options, selecting the Formulas category, and then changing the Maximum
Iterations value to a number greater than 100, setting the Maximum Change value to a
number less than 0.001, or both.
For more information about worksheet calculation options, see Chapter 12.
This example illustrates another factor you should be aware of when you use the Goal Seek
command. The Goal Seek command finds only one solution, even though your problem
might have several. In this case, the value 4 has two square roots: +2 and –2. In situations
like this, the Goal Seek command gives you the solution with the same sign as the
starting value. For instance, if you start by entering a value of –1 in cell A2 before opening the
Goal Seek dialog box, the Goal Seek command reports the solution as –1.999917, instead of
Using the Solver
The Goal Seek command is handy for problems that involve an exact target value that
depends on a single unknown value. For problems that are more complex, you should use
the Solver add-in. The Solver can handle problems that involve many variable cells and
can help you find combinations of variables that maximize or minimize a target cell. It also
specifies one or more constraints—conditions that must be met for the solution to be valid.
The Solver is an add-in. If the Solver button does not appear on the Data tab on the
ribbon, click the File tab, Options, Add-Ins category, and then click the Go button.
Select the Solver Add-In check box, and then click OK to install it.
As an example of the kind of problem that the Solver can tackle, imagine you are
planning an advertising campaign for a new product. Your total budget for print advertising is
$12,000,000; you want to expose your ads at least 800 million times to potential readers;
and you’ve decided to place ads in six publications—we’ll call them Pub1 through Pub6.
Each publication reaches a different number of readers and charges a different rate per
page. Your job is to reach the readership target at the lowest possible cost with the
following additional constraints:
At least six advertisements should run in each publication.
No more than a third of your advertising dollars should be spent on any one