Using Goal Seek

When you're planning or forecasting, you'll often need to work backward from your target to derive the figures you need. For example, when planning your next financial year, you may need to find out how much you need to raise the selling price for your widgets to make an extra $30,000 a year.

To work this out, you can try increasing the unit price for the widgets until your revenue figure is $30,000 higher. But you can save time and effort by using Excel's Goal Seek feature to derive the required price automatically by working backward from the revenue figure.

To derive values using Goal Seek, follow these steps:

1. Open the workbook that contains the data, and navigate to the worksheet that contains the calculation.
2. Make active the cell that contains the formula.
3. Choose Tools Goal Seek from the menu bar to display the Goal Seek dialog box (shown in Figure 11–16 with settings chosen).
   

   Figure 11–16. Use the Goal Seek dialog box to work backward from your desired result to the figure you need.

4. Make sure the Set cell text box contains the right cell. (If you selected the cell in Step 2, it will.) If necessary, change the cell either by typing the correct reference or by double-clicking the current contents of the Set cell text box (to select it) and then clicking the right cell in the worksheet. (You can also click the Collapse Dialog button to collapse the Goal Seek dialog box, but the dialog box is so small anyway that it's usually easier to work around it.)
5. In the To Value text box, type the value you want to get.
6. In the By Changing Cell text box, enter the cell whose value you want to change—in the example, the cell containing the price of the widgets. Again, you can type the cell reference or simply click the cell in the worksheet.
7. Click the OK button. Goal Seek calculates the answer, enters it in the worksheet, and then displays the Goal Seek Status dialog box (see Figure 11–17).
   

   Figure 11–17. When Excel displays the Goal Seek Status dialog box, look at the value in the target cell in the worksheet. Click the OK button if you want to keep the value. Click the Cancel button if you want to revert to the previous value.

   NOTE: If the calculation is straightforward, Goal Seek finds a solution quickly. But if the calculation is more complex, Goal Seek may take a while. If this happens, you can click the Pause button to pause the calculation to see which value Goal Seek is trying at the moment. If you want to follow the individual values Goal Seek is trying, click the Step button to display the next value but keep the calculation paused; keep clicking the Step button as needed to see further values. Click the Resume button when you want Goal Seek to resume the calculation at full speed.

8. Look at the value in the target cell. Click the OK button if you want to keep it. Click the Cancel button if you want to go back to the previous value.