• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 21. Using Formulas and Functions > Using Goal Seek to Find Values

Using Goal Seek to Find Values

After you've constructed a worksheet and built several intricate formulas, you may discover that you can't easily get the answer you're looking for. A formula that uses the PMT function, for example, is designed to produce the total monthly payment when you enter the price and loan details. But what if you want to start with a specific monthly payment and interest rate, and then calculate the maximum loan amount you can afford based on those values? Rather than construct a new formula or use trial-and-error methods to find the right result, use Excel's Goal Seek tool to perform the calculations in one operation.

  1. Start by opening the worksheet that contains the formula you want to work with, and then choose Tools, Goal Seek. Excel displays the Goal Seek dialog box shown in Figure 21.15.

    Figure 21.15.

  2. Fill in the three boxes to match the results you're trying to achieve. In the Set Cell box, enter the address of the formula whose results you want to control. In the To Value box, enter the amount the formula specified in the previous cell should equal. Finally, in the By Changing Cell box, enter the cell that contains the single value you want to change.

  3. When you click OK, Excel runs through all possibilities and displays the Goal Seek Status dialog box, as shown in Figure 21.16. If you look at the worksheet itself, you'll see the values have changed to reflect the result you were looking for.

    Figure 21.16.

  4. Click OK to incorporate the changed data into your worksheet; click Cancel to close the dialog box and restore the original data.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint