Using the Goal Seek Tool


Goal Seek is a useful tool that you can use to achieve a certain value in a cell that contains a formula. The way you do that is to adjust the value of another cell that has a direct effect on the original cell . If I can afford $400 a month, what could I borrow? You can use Goal Seek to see how much you can borrow to make the payment of $500 per month.

How the Goal Seek Tool Works

The following To Do exercise shows you how Goal Seek works. You need to enter data on Sheet2 in the Data Analysis workbook to prepare for using Goal Seek.

To Do: Use the Goal Seek Tool
  1. In Sheet2, type the data shown in the worksheet in Figure 17.8. For the worksheet to look just like the one in the figure, left-align cells C6:C9 and format cells E6:F9 with two decimal places. In the Total column, the following cells contain these formulas:

    • F6 : =E6*C6

    • F7 : =E7*C7

    • F8 : =E8*C8

    • F9 : =E9*C9

    • F11 : =SUM(F6:F10)

    Figure 17.8. Setting up a worksheet with values and totals.

    graphics/17fig08.jpg

  2. Choose Tools, Goal Seek. The Goal Seek dialog box opens, as shown in Figure 17.9. Here's where you tell Excel which cell contains the formula you want to change, the input value, and the cells that you want to change.

    Figure 17.9. The Goal Seek dialog box.

    graphics/17fig09.jpg

  3. Select the cell on the worksheet that contains the formula whose result you want to change. If necessary, click cell F11. The cell reference F11 appears in the Set Cell box.

  4. The value you want the formula to reflect needs to be entered in the To Value box. Click in the To Value box and type 5000 . This step tells Excel to reach a different grand total of 5000 .

  5. Now you need to tell Excel which cell contains the data you want to change. We want Goal Seek to change the quantity ordered for silk. Click in the By Changing Cell box, click the worksheet, and select cell C6. Cell C6 appears in the By Changing Cell box. This step tells Excel to reach a different quantity for silk, given a grand total of 5000 .

  6. Click OK. The Goal Seek Status dialog box pops open , as you see in Figure 17.10. This dialog box gives you several options: stepping through an operation, pausing operations, and seeking additional help. You also see the cell information, the target value, and the current value. As Goal Seek works, you can see the result and step through, pause, or change it as you go.

    Figure 17.10. Goal Seek Status dialog box.

    graphics/17fig10.jpg

  7. Click OK. Goal Seek places the value found into the specified cell.

  8. If this value isn't the one you want, restore the original value by clicking the Undo button on the Standard toolbar.

  9. If you can't decide what to do, click the Redo button on the Standard toolbar to recalculate the goal seek you just undid.

Using Goal Seek on Chart Data

If the value of a data marker (data series) on a chart was generated from a formula, you can change the values in a chart by using the Goal Seek tool. More specifically , you can alter the values generated from formulas in a worksheet in 3D Surface, Radar, and Area charts by using the Tools, Goal Seek option on the worksheet.

The next exercise walks you through using Goal Seek on chart data. In Sheet2, you create a 3D Area chart, using the Chart Wizard to illustrate the items and totals. Then you use Goal Seek to change the chart data for the silk item.

To Do: Use Goal Seek on Chart Data
  1. In Sheet2, select cells D5:D9. Hold down the Ctrl key and select cells F5:F9. This step tells Excel to chart the data in the Item and Total columns .

  2. Use the Chart Wizard tool on the Standard toolbar to create a 3D Area chart on Sheet2, and accept all the defaults in the Chart Wizard dialog boxes. If you need help building the chart, refer to Hour 12, "Adding a Chart." Figure 17.11 shows the 3D Area chart. Now you can use Goal Seek to change the data in the chart. You want to change the data for the silk item.

    Figure 17.11. The 3D Area chart on the worksheet.

    graphics/17fig11.jpg

  3. Click cell F6. This cell contains the formula whose result you want to change.

  4. Choose Tools, Goal Seek. The Goal Seek dialog box opens. In the Set Cell box, you should see cell F6. This cell contains the formula whose result you want changed.

  5. The value you want the formula to reflect needs to be entered in the To Value box. Click in the To Value box and type 1000 . This step tells Excel to reach a different total of 1000 .

  6. Now you need to tell Excel which cell contains the data you want to change. You want Goal Seek to change the quantity ordered for silk. Click in the By Changing Cell box, click the worksheet, and select cell C6. Cell C6 appears in the By Changing Cell box. This step tells Excel to reach a different quantity for silk, given a grand total of 1000 .

    graphics/bookpencil_icon.gif

    When you use the Goal Seek tool to change the values in a chart, you can change the value of only one cell.


  7. Click OK. The Goal Seek Status dialog box pops open. Goal Seek found a solution.

  8. Click OK. Goal Seek places the value found into the specified cell and updates the 3D Area chart. Notice that the silk area on the chart decreased to 1000 , as shown in Figure 17.12.

    Figure 17.12. Changed data in the 3D Area chart.

    graphics/17fig12.jpg

  9. If this value isn't what you want, restore the original values by clicking the Undo button on the Standard toolbar.

  10. If you can't decide what to do, click the Redo button on the Standard toolbar to recalculate the Goal Seek you just undid.

Common Goal Seek Errors

Here are some of the common Goal Seek errors that might occur before you reach a solution:

  • You interrupted the Goal Seek process.

  • You entered the wrong cell reference in the Set Cell box. The cell that contains the formula you want to change should appear in the Set Cell box.

  • You specified the wrong value in the To Value box.

  • The cell reference you entered in the By Changing Cell box is a range of cells. Goal Seek can change only one cell for you.

When Goal Seek stops before a solution is found, an error message appears in the Goal Seek Status dialog box. Click the Pause button, make any changes you desire , and then try goal seeking again by clicking the Continue button.



Sams Teach Yourself Microsoft Office Excel 2003 in 24 Hours
Sams Teach Yourself Microsoft Office Excel 2003 in 24 Hours (Sams Teach Yourself in 24 Hours)
ISBN: 1435276337
EAN: 2147483647
Year: 2003
Pages: 279
Authors: Trudi Reisner

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net