Goal Seeking
Goal Seeking
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
Singlecell 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 28 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 28:
This worksheet
C10: =(1C5)*C4 C11: =PMT(C7/12,C6,C10) C12: =C11*C6 C13: =C12C10
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 fixedrate mortgage loan for 6.00 percent, based on an 80 percent loantovalue (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
Data Tools
WhatIf Analysis
Goal Seek. Excel displays the Goal Seek dialog box, as shown in Figure 29. Completing this dialog box resembles forming the following
Figure 29:
The Goal Seek dialog box.
Almost immediately, Excel announces that it has found the solution and displays the Goal Seek Status box. This box

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}
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.
More about Goal Seeking
If you think about it, you may realize that Excel can't always find a value that produces the result you're looking forsometimes 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 addin.