Microsoft Office Tutorials and References
In Depth Information
Goal seeking serves as a useful feature that works in conjunction with your formulas. If you know
what a formula result should be, Excel can tell you which values of one or more input cells you
need to produce that result. In other words, you can ask a question such as, “What sales increase
is needed to produce a profit of $1.2 million?”
Single-cell goal seeking (also known as backsolving ) represents a rather simple concept. Excel
determines what value in an input cell produces a desired result in a formula cell. You can best
understand how this works by walking through an example.
A goal seeking example
Figure 2-8 shows a mortgage loan worksheet that has four input cells (C4:C7) and four formula
cells (C10:C13). The formulas calculate various values using the input cell. The formulas are
C 11: =PMT(C7/12,C6,–C10)
Figure 2-8: This worksheet presents a simple demonstration of goal seeking.
Imagine that you’re in the market for a new home and you know that you can afford $1,200 per
month in mortgage payments. You also know that a lender can issue a fixed-rate mortgage loan
for 6.00 percent, based on an 80 percent loan-to-value (a 20 percent down payment). The
question is, “What is the maximum purchase price you can handle?” In other words, what value in cell
C4 causes the formula in cell C11 to result in $1,200? You can plug values into cell C4 until C11
displays $1,200. A more efficient approach lets Excel determine the answer.
To answer this question, choose Data
Goal Seek. Excel displays
the Goal Seek dialog box, as shown in Figure 2-9. Completing this dialog box resembles forming
the following sentence: Set cell C11 to 1200 by changing cell C4. Enter this information in the
dialog box by either typing the cell references or by pointing with the mouse. Click OK to begin the
goal seeking process.