Using Goal Seek

Sometimes you'll know the desired result of a problem before you know a crucial piece needed to reach that result. For example, say you hold 200 shares of stock that you bought at $52 a share. You've told yourself you're going to hold onto the stock until your earnings on it reach $100,000. You know what the end result should be ($100,000), but you don't know what price the stock needs to reach to make your earnings equal $100,000. One of the variables , in this case the desired stock price, is unknown.

Calc's goal seek feature is perfect for this type of unknown variable math problem.

The key to using goal seek is to set up all the variables in the spreadsheet (except for the unknown variable you're trying to solve), and enter the necessary formula in the cell that contains your desired goal. Figure 22-8 provides an example of how you'd set up a spreadsheet for using goal seek.

Figure 22-8. Preparing your spreadsheet for using goal seek

Goal seek works by looking at the formula that will be used to reach the desired goal, taking the desired goal value you enter to know what the result of the formula should be, and performing the behind-the-scenes math necessary to produce the unknown variable.

Following is the procedure for using goal seek once your spreadsheet is prepared.

1. Select the cell containing the formula that will result in your desired goal.

2. Choose Tools > Goal Seek.

3. Enter the necessary information in the Goal Seek window. Use Figure 22-9 for guidance.

   Figure 22-9. Entering values in the Goal Seek window

   

4. Click OK.

5. If your formula is set up correctly and the necessary variables are set up in the spreadsheet to support the formula, goal seek tells you it was successful.

6. Click OK in the confirmation window.

   Calc fills in the missing variable to make the formula cell equal the desired goal.

If goal seek wasn't successful, the confirmation window tells you it was unsuccessful and asks if you want to insert the suggested value displayed. It doesn't matter if you accept this value or not, since you'll probably need to double-check your formula and the cells it references to make sure everything is set up correctly, then run goal seek again to insert the correct value for the variable.