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

Chapter 26. 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 might 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've determined your maximum monthly payment, you've shopped around for the best interest rate, and now you want to 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:

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

Figure 26.18.

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.

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

Figure 26.19.

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