Excel 2010 Bible

Part V: Analyzing Data with Excel

762

Single-Cell Goal Seeking

Single-cell goal seeking is a rather simple concept. Excel determines what value in an input cell

produces a desired result in a formula cell. The following example shows you how single-cell goal

seeking works.

A goal-seeking example

Figure 37.1 shows the mortgage loan worksheet used in the preceding chapter. This worksheet has

four input cells (C4:C7) and four formula cells (C10:C13). Originally, this worksheet was used for

a what-if analysis example. This example demonstrates the opposite approach. Rather than supply

different input cell values to look at the calculated formulas, this example lets Excel determine one

of the input values that will produce the desired result.

On the CD
This workbook is available on the companion CD-ROM. The file is named mortgage loan.xlsx.

FIGURE 37.1

This worksheet is a good demonstration of goal seeking.

Assume that you’re in the market for a new home and you know that you can afford an $1,800

monthly mortgage payment. You also know that a lender can issue a 30-year fixed-rate mortgage

loan for 6.50%, based on an 80% loan-to-value (that is, a 20% down payment). The question is

“What is the maximum purchase price I can handle?” In other words, what value in cell C4 causes

the formula in cell C11 to result in $1,800? In this simple 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: