Excel 2019 Bible

Chapter 32: Analyzing Data Using Goal Seeking and Solver

3232

example, you could plug values into cell C4 until C11 displays $1,800. With more complex

models, Excel can usually determine the answer much more efficiently.

To answer the question posed in the preceding paragraph, first set up the input cells to

match what you already know. Specifically:

1. Enter 20% into cell C5 (the down payment percent).


2. Enter 360 into cell C6 (the loan term, in months).


3. Enter 6.5% into cell C7 (the annual interest rate).

Next, choose Data ➪ Forecast ➪ What-If Analysis ➪ Goal Seek. The Goal Seek dialog box

appears. Completing this dialog box is similar to forming a sentence. You want to set cell

C11 to 1800 by changing cell C4. Enter this information in the dialog box either by typing

the cell references or by pointing with the mouse (see Figure 32.2). Click OK to begin the

goal-seeking process.

FIGURE 32.2

The Goal Seek dialog box

In less than a second, Excel displays the Goal Seek Status box, shown in Figure 32.3. This

shows the target value and the value that Excel calculated. In this case, Excel found an

exact value. The worksheet now displays the found value in cell C4 ($355,974). As a result of

this value, the monthly payment amount is $1,800. At this point, you have two options:

■ (^) Click OK to replace the original value with the found value. After doing so, you can
use Undo to return to the original value.
■ (^) Click Cancel to restore your worksheet to the form that it had before you chose Goal
Seek.