Usually after you can create a formula, you can type in new data to see how the formula calculates a new result. However, Excel also offers a feature known as Goal Seeking. With Goal Seeking, you specify the value you want a formula to calculate, and then Excel changes the data in the formula's cell references to tell you what values you need to achieve that goal.
For example, suppose you have a formula that calculates how much money you make every month by selling a product such as cars. Change the number of cars you sell, and Excel calculates your monthly commission. But if you use Goal Seeking, you can specify you want to earn $5,000 for your monthly commission, and Excel will work backward to tell you how many cars you need to sell. As its name implies, Goal Seeking lets you specify a goal and see what number, in a specific cell, needs to change to help you reach your goal.
To use Goal Seeking, follow these steps:
1. Click in the cell that contains a formula.
2. Click the Data tab.
3. Click the What-If Analysis icon in the Data Tools group.
A pull-down menu appears, as shown in Figure 8-6.
The What-If Analysis icon displays the Goal Seek command.
4. Click Goal Seek.
The Goal Seek dialog box appears, as shown in Figure 8-7.
Define your goal in a cell containing a formula.
5. Click in the To Value text box and type a number that you want to appear in the formula stored in the cell that you clicked in Step 1.
6. Click in the By Changing Cell text box and click one cell that contains data used by the formula you chose in Step 1.
Excel displays your cell reference, such as B5, in the Goal Seek dialog box.
7. Click OK.
The Goal Seek Status dialog box changes the data in the cell you chose in Step 6, as shown in Figure 8-8.
The Goal Seek Status dialog box changes the data to reach your desired goal.
8. Click OK (to keep the changes) or click Cancel (to display the original values your spreadsheet had before you chose the Goal Seek command).