Goal seek excel

Use Goal Seek to find the result you want by adjusting an input value

If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.

Note: Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information, see Define and solve a problem by using Solver.

Step-by-step with an example

Let’s look at the preceding example, step-by-step.

Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.

Prepare the worksheet

Open a new, blank worksheet.

First, add some labels in the first column to make it easier to read the worksheet.

In cell A1, type Loan Amount.

In cell A2, type Term in Months.

In cell A3, type Interest Rate.

In cell A4, type Payment.

Next, add the values that you know.

In cell B1, type 100000. This is the amount that you want to borrow.

In cell B2, type 180. This is the number of months that you want to pay off the loan.

Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.

Next, add the formula for which you have a goal. For the example, use the PMT function:

In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don’t enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.

The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.

Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.

In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.

In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.

In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.

Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.

Goal Seek runs and produces a result, as shown in the following illustration.

Finally, format the target cell (B3) so that it displays the result as a percentage.

On the Home tab, in the Number group, click Percentage.

Click Increase Decimal or Decrease Decimal to set the number of decimal places.

Note: Goal Seek works only with one variable input value. If you want to accept more than one input value, for example, both the loan amount and the monthly payment amount for a loan, use the Solver add-in. For more information, see Define and solve a problem by using Solver.

Step-by-step with an example

Let’s look at the preceding example, step-by-step.

Prepare the worksheet

Open a new, blank worksheet.

First, add some labels in the first column to make it easier to read the worksheet.

Читать еще: Если ячейка не пустая то excel

In cell A1, type Loan Amount.

In cell A2, type Term in Months.

In cell A3, type Interest Rate.

In cell A4, type Payment.

Next, add the values that you know.

In cell B1, type 100000. This is the amount that you want to borrow.

In cell B2, type 180. This is the number of months that you want to pay off the loan.

Next, add the formula for which you have a goal. For the example, use the PMT function:

Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

Do one of the following:

In Excel 2016 for Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.

In Excel for Mac 2011: On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.

In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.

In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.

In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.

Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.

Goal Seek runs and produces a result, as shown in the following illustration.

Finally, format the target cell (B3) so that it displays the result as a percentage. Follow one of these steps:

In Excel 2016 for Mac: On the Home tab, click Increase Decimal or Decrease Decimal .

In Excel for Mac 2011: On the Home tab, under Number group, click Increase Decimal or Decrease Decimal to set the number of decimal places.

Goal seek excel

На этом шаге мы рассмотрим параметры этого метода и пример применения .

Метод GoalSeek (Подбор параметра) подбирает значение параметра (неизвестной величины), являющееся решением уравнения с одной переменной. Предполагается, что уравнение приведено к виду: правая часть является постоянной, не зависящей от параметра, который входит только в левую часть уравнения. Вручную метод GoalSeek выполняется с помощью команды Сервис | Подбор параметра (Tools | Goal Seek) . Метод GoalSeek вычисляет корень, используя метод последовательных приближений, результат выполнения которого, вообще говоря, зависит от начального приближения. Поэтому для корректности нахождения корня надо позаботиться о корректном указании этого начального приближения.

Таблица 1. Аргументы метода GoalSeek

Аргумент	Назначение
Объект	Ячейка, в которую введена формула, являющаяся правой частью решаемого уравнения. В этой формуле роль параметра (неизвестной величины) играет ссылка на ячейку, указанную в аргументе ChangingCell
Goal	Значение левой части решаемого уравнения, не содержащей параметра
ChangingCell	Ссылка на ячейку, отведенную под параметр (неизвестную величину). Значение, введенное в данную ячейку до активизации метода GoalSeek , рассматривается как начальное приближение к искомому корню

Точность, с которой находится корень и предельно допустимое число итераций, используемых для нахождения корня, устанавливается свойствами MaxChange и MaxIterations объекта Application . Например, определение корня с точностью до 0,0001 максимум за 1000 итераций устанавливается инструкцией:

Вручную эти величины устанавливаются на вкладке Вычисления (Calculation) диалогового окна Параметры (Options) , вызываемого командой Сервис | Параметры (Tools | Options) .

Приведем соответствие между аргументами метода GoalSeek и нахождения корня уравнения х 2 = 3 на рабочем листе вручную при помощи команды Сервис | Подбор параметра (Tools | Goal Seek) .

- Ячейку A1 отведем под неизвестную. Команда Подбор параметра (Goal Seek) находит корень уравнения методом последовательных приближений, результат которого зависит от начального приближения к корню. Команда Подбор параметра (Goal Seek) воспринимает значение, первоначально введенное в ячейку A1 , за начальное приближение. Будем считать, что начальное приближение к корню равно 0. Введем 0 в ячейку A1 . В методе GoalSeek аргумент ChangingCell отвечает за ссылку на ячейку, отведенную под неизвестную. В данном случае аргументу ChangingCell присваиваем Range («A1») .
- Ячейку А2 отведем под левую часть уравнения. При решении уравнения с помощью команды Подбор параметра (Goal Seek) уравнение надо преобразовать к такому виду, чтобы в правой его части содержалась только постоянная, не зависящая от неизвестной, которая должна входить только в его правую часть. В данном случае в ячейку А2 введем формулу =А1^2 (рисунок 1).

Рис.1. Ввод данных на рабочем листе при решении нелинейного уравнения

В методе GoalSeek диапазон, к которому применяется метод, отвечает за ссылку на ячейку, отведенную под левую часть уравнения, содержащую неизвестную. В данном случае метод GoalSeek применяется к диапазону Range («A2») .

Выберите команду Сервис | Подбор параметра (Tools | Goal Seek) . В появившемся диалоговом окне Подбор параметра (Goal Seek) (рисунок 2):

Рис.2. Диалоговое окно Подбор параметра

В поле Установить в ячейке (Set cell) введите ссылку на ячейку А2 ;
В поле Изменяя значение ячейки (By changing cell) — ссылку на ячейку A1 ;
В поле Значение введите 3. В поле Значение (То value) вводится величина правой части уравнения.

В методе GoalSeek аргумент Goal отвечает за правую часть уравнения. В данном случае присвоим аргументу Goal значение 3. Таким образом, имеем:

Рис.3. Результат выполнения команды

В силу того, что решение находится приближенно с указанной точностью, в ячейке А2 получилось 2.999325, а не ровно 3. Увеличивая точность, можно найти лучшее приближение к корню.

На следующем шаге мы рассмотрим метод Sort .

How to use Goal Seek in Excel for What-If analysis

The tutorial explains how to use Goal Seek in Excel 2016, 3013 and 2010 to get the formula result you want by changing an input value.

What-If Analysis is one of the most powerful Excel features and one of the least understood. In most general terms, What-If Analysis allows you to test out various scenarios and determine a range of possible outcomes. In other words, it enables you to see the impact of making a certain change without changing the real data. In this particular tutorial, we will focus on one of Excel’s What-If Analysis tools — Goal Seek.

What is Goal Seek in Excel?

Goal Seek is Excel’s built-in What-If Analysis tool that shows how one value in a formula impacts another. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.

The best thing about Excel Goal Seek is that it performs all calculations behind the scenes, and you are only asked to specify these three parameters:

Formula cell
Target/desired value
The cell to change in order to achieve the target

The Goal Seek tool is especially useful for doing sensitivity analysis in financial modeling and is widely used by management majors and business owner. But there are many other uses that may prove helpful to you.

For instance, Goal Seek can tell you how much sales you have to make in a certain period to reach $100,000 annual net profit (example 1). Or, what score you must achieve for your last exam to receive an overall passing score of 70% (example 2). Or, how many votes you need to get in order to win the election (example 3).

On the whole, whenever you want a formula to return a specific result but are not sure what input value within the formula to adjust to get that result, stop guessing and use the Excel Goal Seek function!

How to use Goal Seek in Excel

The purpose of this section is to walk you through how to use the Goal Seek function. So, we’ll be working with a very simple data set:

The above table indicates that if you sell 100 items at $5 each, minus the 10% commission, you will make $450. The question is: How many items do you have to sell to make $1,000?

Let’s see how to find the answer with Goal Seek:

Set up your data so that you have a formula cell and a changingcell dependent on the formula cell.
Go to the Data tab >Forecast group, click the What if Analysis button, and select Goal Seek…
In the Goal Seek dialog box, define the cells/values to test and click OK:
- Set cell — the reference to the cell containing the formula (B5).
- To value — the formula result you are trying to achieve (1000).
- By changing cell — the reference for the input cell that you want to adjust (B3).

The Goal SeekStatus dialog box will appear and let you know if a solution has been found. If it succeeded, the value in the «changing cell» will be replaced with a new one. Click OK to keep the new value or Cancel to restore the original one.

In this example, Goal Seek has found that 223 items (rounded up to the next integer) need to be sold to achieve a revenue of $1,000.

If you are not sure you will be able to sell that many items, then maybe you can reach the target revenue by changing the item price? To test this scenario, do Goal Seek analysis exactly as described above except that you specify a different Changing cell (B2):

As the result, you will find out that if you increase the unit price to $11, you can reach $1,000 revenue by selling only 100 items:

Excel Goal Seek does not change the formula, it only changes the input value that you supply to the By changing cell box.
If Goal Seek is not able to find the solution, it displays the closest value it has come up with.
You can restore the original input value by clicking the Undo button or pressing the Undo shortcut ( Ctrl + Z ).

Examples of using Goal Seek in Excel

Below you will find a few more examples of using the Goal Seek function in Excel. The complexity of your business model does not really matter as long as your formula in the Set cell depends on the value in the Changing cell, directly or through intermediate formulas in other cells.

Example 1: Reach the profit goal

Problem: It is a typical business situation — you have the sales figures for the first 3 quarters and you want to know how much sales you have to make in the last quarter to achieve the target net profit for the year, say, $100,000.

Solution: With the source data organized like shown in the screenshot above, set up the following parameters for the Goal Seek function:

Set cell — the formula that calculates the total net profit (D6).
To value — the formula result you are looking for ($100,000).
By changing cell — the cell to contain the gross revenue for quarter 4 (B5).

Result: The Goal Seek analysis shows that in order to obtain $100,000 annual net profit, your fourth-quarter revenue must be $185,714.

Example 2: Determine the exam passing score

Problem: At the end of the course, a student takes 3 exams. The passing score is 70%. All the exams have the same weight, so the overall score is calculated by averaging the 3 scores. The student has already taken 2 out of 3 exams. The question is: What score does the student need to get for the third exam to pass the entire course?

Solution: Let’s do Goal Seek to determine the minimum score on exam 3:

Set cell — the formula that averages the scores of the 3 exams (B5).
To value — the passing score (70%).
By changing cell — the 3 rd exam score (B4).

Result: In order get the desired overall score, the student must achieve a minimum of 67% on the last exam:

Example 3: What-If analysis of the election

Problem: You are running for some elected position where a two-thirds majority (66.67% of votes) is required to win the election. Assuming there are 200 total voting members, how many votes do you have to secure?

Currently, you have 98 votes, which is quite good but not sufficient because it only makes 49% of the total voters:

Solution: Use Goal Seek to find out the minimum number of «Yes» votes you need to get:

Set cell — the formula that calculates the percentage of the current «Yes» votes (C2).
To value — the required percentage of «Yes» votes (66.67%).
By changing cell — the number of «Yes» votes (B2).

Result: What-If analysis with Goal Seek shows that to achieve the two-thirds mark or 66.67%, you need 133 «Yes» votes:

Excel Goal Seek not working

Sometimes Goal Seek is not able to find a solution simply because it does not exist. In such situations, Excel will get the closest value and inform you that Goal Seeking may not have found a solution:

If you are certain that a solution to the formula you are trying to resolve does exist, check out the following troubleshooting tips.

1. Double check Goal Seek parameters

First off, make sure the Set cell refers to the cell containing a formula, and then, check if the formula cell depends, directly or indirectly, on the changing cell.

2. Adjust iteration settings

In your Excel, click File > Options > Formulas and change these options:

Maximum Iterations — increase this number if you want Excel to test more possible solutions.
Maximum Change — decrease this number if your formula requires more accuracy. For example, if you are testing a formula with an input cell equal to 0 but Goal Seek stops at 0.001, setting Maximum Change to 0.0001 should fix the issue.

The below screenshot shows the default iteration settings:

3. No circular references

For Goal Seek (or any Excel formula) to work properly, the involved formulas should not be co-dependent on each other, i.e. there should be no circular references.

That’s how you perform What-If analysis in Excel with the Goal Seek tool. I thank you for reading and hope to see you on our blog next week!

Goal Seek

If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result.

Goal Seek Example 1

Use Goal Seek in Excel to find the grade on the fourth exam that produces a final grade of 70.

1. The formula in cell B7 calculates the final grade.

2. The grade on the fourth exam in cell B5 is the input cell.

3. On the Data tab, in the Forecast group, click What-If Analysis.

4. Click Goal Seek.

The Goal Seek dialog box appears.

5. Select cell B7.

6. Click in the ‘To value’ box and type 70.

7. Click in the ‘By changing cell’ box and select cell B5.

Result. A grade of 90 on the fourth exam produces a final grade of 70.

Goal Seek Example 2

Use Goal Seek in Excel to find the loan amount that produces a monthly payment of $1500.

1. The formula in cell B5 calculates the monthly payment.

Explanation: the PMT function calculates the payment for a loan. If you’ve never heard of this function before, that’s OK. The higher the loan amount, the higher the monthly payment. Assume, you can only afford $1500 a month. What is your maximum loan amount?

2. The loan amount in cell B3 is the input cell.

3. On the Data tab, in the Forecast group, click What-If Analysis.

4. Click Goal Seek.

The Goal Seek dialog box appears.

5. Select cell B5.

6. Click in the ‘To value’ box and type -1500 (negative, you are paying out money).

7. Click in the ‘By changing cell’ box and select cell B3.

Result. A loan amount of $250,187 produces a monthly payment of $1500.

Goal Seek Precision

Goal seek returns approximate solutions. You can change the iteration settings in Excel to find a more precise solution.

1. The formula in cell B1 calculates the square of the value in cell A1.

2. Use goal seek to find the input value that produces a formula result of 25.

Result. Excel returns an approximate solution.

3. On the File tab, click Options, Formulas.

4. Under Calculation options, decrease the Maximum Change value by inserting some zeros. The default value is 0.001.

6. Use Goal Seek again. Excel returns a more precise solution.

More about Goal Seek

There are many problems Goal Seek can’t solve. Goal Seek requires a single input cell and a single output (formula) cell. Use the Solver in Excel to solve problems with multiple input cells. Sometimes you need to start with a different input value to find a solution.

1. The formula in cell B1 below produces a result of -0.25.

2. Use Goal Seek to find the input value that produces a formula result of +0.25.

Result. Excel can’t find a solution.

4. Start with an input value greater than 8.

5. Use Goal Seek again. Excel finds a solution.

Explanation: y = 1 / (x — 8) is discontinuous at x = 8 (dividing by 0 is not possible). In this example, Goal seek cannot reach one side of the x-axis (x>8) if it starts on the other side of the x-axis (x

голоса

Рейтинг статьи