Excel 2019 Bible

Part IV: Managing and Analyzing Data

Using 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 sin-

gle-cell goal seeking works.

Looking at a goal-seeking example

Figure 32.1 shows the mortgage loan worksheet used in the preceding chapter. This work-

sheet has four input cells (C4:C7) and four formula cells (C10:C13). Originally, this work-

sheet 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.

FIGURE 32.1

A mortgage calculator with input cells and formula cells

This workbook is available on this book’s website at http://www.wiley.com/go/excel2019bible. The

file is named mortgage loan.xlsx.

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

$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 a 20% down payment. The question is, “What is

the maximum purchase price I can afford?” In other words, what value in cell C4 (purchase

price) causes the formula in cell C11 (monthly payment) to result in $1,800? In this simple