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

+2.000023.

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.

Note

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

publication.

●