Friday, December 20, 2013

How to use ‘Goal Seek’ feature in Microsoft Excel 2007?

Leave a Comment
Goal Seek feature in Microsoft Excel 2007 is a very interesting tool which shows the impact of one data item in the formula on another. In other words, if we change the goal, ‘Goal Seek’ feature let us find out how the values required for calculating the goal will be impacted.

Understanding ‘Goal Seek’ feature of Microsoft Excel 2007

Let us consider that we are calculating Speed of car using excel. We have following data as inputs:
Distance= 50, Time=10. 
Our goal is to calculate Speed. Speed= D/T= 50/10 = 5

Now suppose, I want to change my goal (Speed) to 8. With what variation in distance, this goal can be achieved? In other words, by what change in distance we can we seek our goal of speed from 5 to 8?
Goal Seek feature of Excel serves this purpose.

Steps to use Goal Seek Feature in Microsoft Excel 2007

1.       In excel click on the ‘Data’ tab.
2.       On the right of the panel click on the What-If Analysis drop down.
3.       Select ‘Goal Seek’ feature from the drop down. A pop-up window will appear which will ask for some inputs as shown below:

a.       Set Cell:  Set the value of the cell containing the formula. In our case, Speed (B4) is calculated by formula i.e. B2/B3. So, set ‘Set Cell’ to B4.
b.      To Value: Set the value to new goal which we want to achieve. In our case we want speed to be 8. So set ‘To value’ to 8.
c.       By Changing Cell: Set the cell by variation in which we want to achieve our new goal of Speed=8. In our cause we want to look for the change in distance. So set ‘By Changing Cell’ to B2.

d.      Click on the OK button and Goal Seek feature will provide you the solution. You will get the changed value for Distance(=80) for Speed=8.


Post a Comment