Goal Seek in Microsoft Excel

Provided by the FMS Development Team

Goal Seek is a feature in Microsoft Excel that will determine a value for you, based on a desired goal. Goal seek works using at least 2 fields, one of which is a calculation.

For example, suppose that you wanted to know what value is 25% of 125. A percent is always the part divided by the whole, of which we know the two latter pieces, but need to now determine the former piece of information.

To create this example in Excel, create a new worksheet, and across the first three columns, put the following text to make headers:

A1: Part
B1: Whole
C1: Percent

Given that you know the Whole value, 125, enter it into cell B2.

In cell C2, put the following formula, which will calculate the percent: =A2/B2

Now you are ready to Goal Seek. Click into cell C2, and from the Main Menu choose Tools / Goal Seek. This will open the Goal Seek dialog box, which has the following three parameters:

• Set Cell: A calculation cell in which the Goal is attributed.
• To Value: The value in which the Set Cell should result.
• By Changing Cell: The value to be changed to make it such that the Set Cell results in the target value.

In this example, the 'Set Cell' is the percent field, thus C2 would be its entry. The 'To Value' is .25, which is the 25%. The 'By changing cell' would be A2, as we want to determine the Part field.

Click OK on the dialog box and the Goal seek performs the math necessary to determine the value. After Goal Seek completes, it should change A2 to 31.25 as 31.25 / 125 = 25%.

Return to Tips & Techniques Page