This tutorial explains how to use Goal Seek in Excel. It includes explanation of this feature with several practical examples.

**What is Goal Seek?**

Goal Seek is an inbuilt excel utility that solves an equation and helps you to answer this question "What would be the input value to get the desired result".

It asks us to input three parameters :

- Formula in a cell
- Target / Desired Value
- Which cell to change

*Let's explain it with case studies :*

**Case Study I**

Suppose you have a data set in which there are some missing values. You need to calculate the

**number of non-missing values**required if you need to maintain 70% of non-missing values in your data. See the snapshot of data shown in the image below-Excel : Goal Seek |

**Data Preparation for Goal Seek**

The following are the steps to prepare data before running Goal Seek.

- Type the text
**'No. of Non-Missing Values'**in cell B3 and enter any dummy value (let's say 117) corresponding to it in cell C3. We'll calculate the exact value in the following steps. - Enter the text
**'No. of Missing Values'**in cell B4**actual figure**(let's say 50) - Type
**'% of Non-Missing Values'**in cell B5 and enter the following formula in cell C5. - Go to the
**'Data' tab**and click on**'What-If Analysis' button**and**Goal Seek**under it. See and follow the instructions shown in the image above. - When you click on
**Goal Seek**button, it will open a box in which you need to provide cell reference and target value. The explanation is provided below -

C3/(C3+C4)

Goal Seek Menu |

**Q. What would be the number of non-missing values if we want to get 60% of non-missing values?**

Goal Seek Parameters |

**Set cell :**Cell reference wherein formula is entered. In this case, give cell reference of

**C5**.

**To value :**Target/ Desired value. In this case, it is

**60%**. Make sure you enter % after 60. You can also type 0.6 instead of 60%

**By changing cell :**reference to the cell that has the value you want to change. In this example, it is reference of

**cell C3**.

Once you have filled the above 3 inputs, click on

**'Ok'**button and then Excel would find solution for you and show you the result in cell**C3**Goal Seek Solution |

**Solution :**We require total

**75 non-missing values**to have 60% non-missing percentage.

**How can we do it without Goal Seek?**

We can calculate it through building mathematical equations -

**Step I :**C3 / (C3+C4) = 0.6

**Step II :**(C3 * 0.6) + ( C4 * 0.6) = C3

**Step III :**(1 - 0.6) * C3 = C4*0.6

**Step IV :**C3 = (C4*0.6) / (1 - 0.6)

If you enter equation of step IV in the formula

**=(C4*0.6)/(1-0.6)**, it would return a desired value.

**Is the above solution correct?**

Answer is

**NO**. It is because the number of missing values are dependent on non-missing values. Both makes a complete dataset. For example, you have a data set which comprises of total 150 records. If you fill 15 missing records to some value, it would increase the number of non-missing records by 15 and the number of missing records would accordingly fall by 15 records. To workaround this issue, we need to include total number of records as a constraint. See the solution below.**Add a Constraint in Goal Seek**

Since we have assumed

**150**total number of rows/records in the dataset, we can set this constraint in the formula.Goal Seek Constraint |

- Enter a formula
**=150-C3**in cell C4 (As shown in the image above) - Run Goal Seek Analysis again (Follow the instructions below)

Goal Seek : Instructions |

*In this case, it returns a solution which also considers the number of cases in data and make sure it's always equal to 150.*

**Important Points**

- The cell reference in
**Set cell: box**must contain a formula. Otherwise, it would return an error. - You cannot give cell reference in
**'To value:' box.**You need to enter a value. - The cell reference in
**'By changing cell:'**cannot contain a formula. It must be a typed value.

**Case Study II**

You can usePMT() functionto calculate the payment for a loan.

**Loan Instalment =**PMT(Interest Rate, No. of Payments, Loan Amount)

In this case, we need to calculate

**'No. of Payments'**which is a parameter in the function itself.

**Data Preparation**

- Enter figures of Loan Amount, No. of Payments (Monthly) and Annual Interest Rate from cell C3 through C5.
**Put any dummy value against No. of Payments. We'll calculate the correct value later.** - Enter formula
**=PMT(C5/12,C4,C3)**in cell C6 (Refer the image above). Since C5 is annual interest rate, we need to divide it by 12 - Go to
**Data tab**and then click on**What-If Analysis**and select**Goal Seek** - In
**Set cell:,**enter reference of cell C6. - In
**To value:,**enter -4000. Make sure it is negative since it is a payment. - In
**By changing cell:,**give cell reference of C4**( Refer figure 2)**

Goal Seek - Loan Payment |

Figure 2 |

**Solution :**Goal Seek found the solution and it is required to pay

**50 monthly payments when $4000 is paid monthly in installment.**

**Endnotes**

Goal Seek is very helpful for quickly solving any financial and statistical formula for a

**single**unknown value. It saves a lot of time when it is difficult to derive a formula to calculate unknown value. As shown in the second case study, it takes time to transform equation of loan payment and calculate the terms without using Goal Seek. If you have a complex problem which contains multiple equations and multiple constraints and you are asked to find the optimal solution, you should look for Excel's Solver Add-In instead of Goal Seek. Excel Solver is a powerful method to solve optimization problem.

This tutorial is very helpful..

ReplyDeletehttp://tutorialway.com/use-goal-seek-in-excel/