**How to use** **Goal Seek**** in Excel**

to do What–If Analysis

**How to use**

**Goal Seek**

**in Excel**

to do What–If Analysis

to do What–If Analysis

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

‘Goal Seek’ is an awesome **‘What-If Analysis’ tool **in Excel.

What-If Analysis helps you see *how changing a cell’s value can affect the overall outcome *of an associated formula.

There are three available ‘What-If Analysis’ tools:

- Scenarios
- Goal Seek
- Data Tables

In this tutorial, we’ll talk about the ‘Goal Seek’ tool in Excel and we’ll show you how you can use this tool to solve real-life problems.

Let’s get this started!

**What is the Goal Seek tool?**

‘Goal Seek’ is a **built-in tool **in Excel that would help you **find the right value to get your desired result**.

You can think of it as some sort of a *reverse tool *wherein you have the (desired) result but you don’t have the right value to get at that result. *Normally, the result is what you have to find from the data set.*

Here are a few examples of real-life problems that can be solved by the Goal Seek tool:

- Finding out the passing grade
- Needed votes for the election
- Finding the affordable loan amount (or interest rate)

Don’t worry. We’ll run through all of them later.

**How to use Goal Seek in Excel**

The Goal Seek tool is simple to use.

There are three parameters you should know about:

**Set cell***(the formula of the result)***To value***(the desired result of the formula)***By changing cell***(the value you want to adjust)*

Now, let’s apply them in a real-life scenario.

*This student whose grades are shown below would like to know how much he needs to score on the last quiz so he’ll get at least an average grade of 75. Currently, his average stands at a failing grade, 71.50.*

The first thing to do is to **click **** ‘Data’ **on the tab list.

From there, **click the ***‘What-If Analysis’ ***icon **on the *‘Forecast’ ***group**. **Select **** ‘Goal Seek’ **from the dropdown.

After that, the *‘Goal Seek’ ***window **will open where you input the needed parameters.

From the data set, **enter the appropriate reference and value **on the window.

**Set cell:****B7***(the location of the formula)***To value:****75***(the desired result)***By changing cell:****B6***(the value you want to change or adjust)*

Then, **hit ‘Enter’** and watch the magic of ‘Goal Seek’ unfold!

The student needs to score

at least 89on the last quiz if he hopes to pass. 😊

**Goal Seek on election analysis**

‘Goal Seek’ can also be used to **calculate the number of votes **needed to win.

Let’s say you’re running for a local position where you need two-thirds (66.7%) of the majority to win. As of now, you have 256 voters out of 850 voters.

*How many votes do you need to win (get at least 66.7% votes out of the total votes)?*

Here’s what you should enter on the ‘Goal Seek’ window:

**Set cell: C2***(the location of the formula)***To value:****0.667***(the desired result)***By changing cell:****B2***(the value you want to change or adjust)*

Then, **press **** ‘Enter’ **to run the calculation.

You need *567 votes* to win.

**Using Goal Seek in Payments**

First off, there’s actually a way you can calculate your **periodic loan payment** in Excel through the PMT function.

*But what if you’re still evaluating how much you should loan or at what interest depending on your income?*

For example, you can only afford to pay $5,000 annually for 5 years with an 8% interest. Now, you want to know how much money you can borrow from those conditions.

*(Don’t worry about the current ‘Annual Payment’. It’s automatically calculated based on the values above it.)*

Here’s what you should enter on the ‘Goal Seek’ window:

**Set cell:****B5***(the location of the formula)***To value:****-5000***(the desired result)***By changing cell:****B4***(the value you want to change or adjust)*

B5 contains the PMT equation: *=PMT(rate, nper, pv, [fv], [type])*

The value is *negative *to be in line with the cash flow model. The value is what you’re supposed to ‘pay’ annually.

**Hit **** ‘Enter’ **to run the tool.

The maximum amount you can loan if you want to stay within the budget is *$21,647 (rounded to the nearest integer)*.

But what if it’s about the interest rate? Like you have already decided to loan $20,000 payable in 10 years and you would like to pay $3,000 yearly.

You now like to know the annual interest rate to make this arrangement possible.

*(Don’t worry about the current ‘Annual Payment’. It’s automatically calculated based on the values above it.)*

Here’s what you should enter on the ‘Goal Seek’ window:

**Set cell:****B5***(the location of the formula)***To value:****-3000***(the desired result)***By changing cell:****B2***(the value you want to change or adjust)*

**Hit **** ‘Enter’** to see the results!

The answer is *8%.*

**Error: Not have found a solution**

As with any other functions or tools in Excel, it’s possible that something could go wrong.

There are times when ‘Goal Seek’ returns a ‘not have found a solution’. This could be due to the fact that a solution doesn’t really exist.

However, if you’re sure that a solution does exist, there are a few things you can check:

**1. Check the parameters and values**

Every time your handling a function and a tool that includes references, make sure your reference is accurate. If not, whatever adjustment you make, you’ll always get the wrong answer.

**2. Change iterative calculation**

There’s a setting in Excel where you can adjust the number of possible solutions Excel will calculate as well as its accuracy.

To *change them*, **click ‘File’** from the tab list.

Then, **click ‘Options’** at the bottom.

On the **‘Excel Options’** window, **click ‘Formulas’** at the left-hand sidebar of the window.

There are 2 settings you can tweak to adjust the iterative calculation:

**Maximum Iterations***(the number of possible solutions; the higher the number the more iterations)***Maximum Change***(accuracy; the lower the number the higher the accuracy)*

**3. No circular reference**

A circular reference occurs when a formula refers back to its own cell directly or indirectly. ‘Goal Seek’ doesn’t work when there are co-dependent formulas involved.

**Conclusion**

What-If Analysis tools like ‘Goal Seek’ is one of the most useful features in Excel yet also one of the least used and unpopular.

Learning how to use such a tool will save you time doing trial and error trying to come up with the right value to get the desired result.