Many spreadsheets contain formulas that enable you to ask questions, such as, "What would be the total profit if sales increase by 20 percent?" If you set up your worksheet properly, you can change the value in one cell to see what happens to the profit
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
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.
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
Figure 2-8: This worksheet
C10: =(1-C5)*C4 C11: =PMT(C7/12,C6,-C10) C12: =C11*C6 C13: =C12-C10
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
Figure 2-9: The Goal Seek dialog box.
Almost immediately, Excel announces that it has found the solution and displays the Goal Seek Status box. This box
Click OK to replace the original value with the found value.
Click Cancel to restore your worksheet to its original form before you chose Goal Seek.
If you think about it, you may realize that Excel can't always find a value that produces the result you're looking for-sometimes a solution doesn't exist. In such a case, the Goal Seek Status box informs you of that fact. Other times, however, Excel may report that it can't find a solution even though you believe one exists. In this case, you can adjust the current value of the changing cell to a value closer to the solution, and then
Like all computer programs, Excel has limited precision. To
In some cases, multiple values of the input cell produce the same desired result. For example, the formula =A1
2 returns 16 if cell A1 contains either
4 or +4. If you use goal seeking when two solutions exist, Excel gives you the solution that is
Perhaps the main limitation of the Goal Seek command is that it can find the value for only one input cell. For example, it can't tell you what purchase price and what down payment percent result in a particular monthly payment. If you want to change more than one variable at a time, use the Solver add-in.
Excel 2007 Bible
John Walkenbach's Favorite Excel 2007 Tips and Tricks (Mr. Spreadsheet's Bookshelf)
Learn Excel 2007 Essential Skills with The Smart Method: Courseware tutorial for self-instruction to beginner and intermediate level
Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques