Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

The Goal Seek feature in Excel 2007 is a what-if analysis tool that enables you to find the input values needed to achieve a goal or objective. To use Goal Seek, you select the cell containing the formula that will return the result you’re seeking and then indicate the target value you want the formula to return and the location of the input value that Excel can change to reach the target.

The steps below follow a specific example for using Goal Seek to help you better understand how to use this feature. Refer to the figures for guidance. To use Goal Seek to find out how much sales must increase to return a net income of $300,000 in the first quarter, follow these steps:

Select the cell containing the formula that will return the result you’re seeking; in this example, cell B7.

Select the cell containing the formula that will return the result you’re seeking; in this example, cell B7.

This cell contains the formula that calculates the forecast for the first quarter of 2008.

On the Data tab, choose What-If Analysis→Goal Seek in the Data Tools group.

This action opens the Goal Seek dialog box. Because cell B7 is the active cell when you open this dialog box, the Set Cell text box already contains the cell reference B7.

Select the To Value text box and enter the goal

This is the value you want the formula in the Set Cell box to reach. In this example, it’s 300000.

Select the By Changing Cell text box and select the cell that you want to change

Excel will change the value in this cell reference to try to reach the goal in the To Value box. In this example, cell B3 contains the first quarter sales. The absolute cell address, $B$3, appears in the text box.

Click OK.

Click OK.

Excel displays the Goal Seek Status dialog box along with the results. In this example, Excel increases the sales in cell B3 from $250,000 to $576,923.08, which, in turn, returns $300,000 as the income in cell B7.

If you want to keep the values entered in the worksheet as a result of goal seeking, click OK.

If you want to return to the original values, click the Cancel button instead. Notice that because all of the values in this table are formulas that ultimately are derived from the value in cell B3, all of the values changed when that cell value was updated during the Goal Seek process.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: