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 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:
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 23.18.
Figure 23.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 23.19. If you look at the worksheet itself, you'll see the values have changed to reflect the result shown here.
Figure 23.19.
Click OK to incorporate the changed data into your worksheet; click Cancel to close the dialog box and restore the original data.
If your problem is more complex and can't be solved by changing a single cell, use the Solver add-in. Like other Excel add-ins, you must install this option before it's available on the Tools menu; choose Tools, Add-Ins, and click the Solver option to install it for the first time. Then choose Tools, Solver to display the Solver Parameters dialog box, as shown in Figure 23.20.
Figure 23.20. Use the Solver Parameters dialog box to specify more complex conditions for working backward to a formula's solution.
Select the cell that you want to adjust in the Set Target Cell box, click the Max, Min, or Value Of box, and enter a comparison amount. In the By Changing Cells box, select the cells you want to adjust. Note that unlike the Goal Seek feature, you can specify multiple cells here. Finally, enter any constraints you want to impose on the solution; for example, you can specify a maximum or minimum value for one or more of the changing cells. Click the Solve button to begin calculating.
When the Solver utility completes its calculation, it displays the Solver Results dialog box, shown in Figure 23.21. If Solver reports an error message, adjust the constraints and try again. If Solver successfully found a solution, you have three choices: Select the Keep Solver Solution option and click OK to change the values in your worksheet; choose the Restore Original Values option and click OK to cancel all changes; or click the Save Scenario button to create a worksheet scenario using the Solver results.
Figure 23.21. The Solver Results dialog box shows the results of a formula's calculations.
→ For a detailed discussion of workbook scenarios, see "Storing Multiple Scenarios in a Single Workbook".