Goal Seek In Excel 2010

This article is about Goal Seek in Excel 2010.Excel is very useful tool for collecting and analyzing of data. It has many built in functions and tools which can be used in different scenarios to get valuable inferences. Amongst them is Goal Seek. This is basically part of what if analysis element. In goal seek part of what if analysis we want to get information about particular cell value by changing just one cell values. For example we have estimated sale $ 2000000 which is fixed, we have two variables Unit price and Units sold. Our object of this analysis is to decide about sale price per unit. We start our analysis with Unit Price $200 and Units sold 1000.

Goal Seek in Excel 2010

We have formula in cell B4

=B2/B3

Result: (2000000/200.00) =1000

Now we use goal seek to get different unit price for different sale volume (units sold). Go to Data Tab, from what –if-analysis drop down option click on Goal Seek.

Goal seek excel 2010

Goal Seek Dialogue box opens, asking for required values.

Excel Goal Seek

It needs three inputs:

Set Value:  The cell which contains the formula. In our example it is cell B4 in Scenario1 and C4 in Scenario2 and so on.

Excel 2010 Goal Seek

To Value:  It is value according to which we want to see the desired result. By changing it the result also changes in change cell.

By changing cell: In this box we enter the cell which will change. In our example it is cell B3 in Scenario1 and C3 in Scenario2 and so on.

As we want see price changes with change in units sold, so we changed units sold and entered 15000 in To Value box. In Scenario1 we entered cell B4 in Set cell box, in To Value box entered 15000 and in By Changing cell box entered cell B3.

Goal Seek Status box popup showing Target and Current Value. Excel also showing results.

Excel Goal Seek

The unit price value is now $133.33 for corresponding units sold figure 15000.

For scenario2 repeat the above Goal Seek steps. The result is

Goal Seek 2010 Excel

For scenario3 repeat the above Goal Seek steps. The result is

 

2010 Excel Goal Seek

And finally we have following result for all twelve scenarios.

Goal Seek in excel

Goal Seek VBA

We can automate the above goal seek process with help of Excel Visual Basic for Application (VBA) coding. VBA coding for the above Goal Seek is

Sub Example1()
 
Range("B4").GoalSeek Goal:=15000, changingcell:=Range("B3")
 
Range("C4").GoalSeek Goal:=20000, changingcell:=Range("C3")
 
Range("D4").GoalSeek Goal:=25000, changingcell:=Range("D3")
 
Range("E4").GoalSeek Goal:=30000, changingcell:=Range("E3")
 
Range("F4").GoalSeek Goal:=35000, changingcell:=Range("F3")
 
Range("G4").GoalSeek Goal:=45000, changingcell:=Range("G3")
 
Range("H4").GoalSeek Goal:=50000, changingcell:=Range("H3")
 
Range("I4").GoalSeek Goal:=55000, changingcell:=Range("I3")
 
Range("J4").GoalSeek Goal:=60000, changingcell:=Range("J3")
 
Range("K4").GoalSeek Goal:=65000, changingcell:=Range("K3")
 
Range("L4").GoalSeek Goal:=67000, changingcell:=Range("L3")
 
Range("M4").GoalSeek Goal:=69000, changingcell:=Range("M3")                
 End Sub

If you found this article about Goal Seek In Excel 2010 useful then comment in comments section. Our site has many useful VBA codes for fast execution of your MS Excel based tasks, visit VBA section of our blog and automate your routine tasks.

Article written by

This blog is administered by Farzand A, he is also the main content contributor.This guy is in relationship with Ms Excel for last 10 years. During this period of romance he has developed many excel based applications for accounting, statistic and other data intense fields. This blog will serve as guide to Excel users of all stages. You will find more information here as compared to 50 0r $100 MS Excel book. Prime advantage of this blog is 24 hours availability for answering your questions. So enjoy free surfing ! G00GLE+

Please comment with your real name using good manners.

Leave a Reply

Follow

Get every new post delivered to your Inbox

Join other followers