Goal Seek selection of parameters Excel and examples of its use

«Goal Seek» – is the limited functionality version of the add option «Finding of the solution». This is the part of the block tool task «DATA»-«Data Tools»-«What-If Analysis».

In the simplified form, its function can be formulated as follows: to find the meanings, which must be entered in the single formula, to obtain the desired (known) result.



Where is «parameter selection» in Excel

For example, you know the result of some kind of formula. There are also input data. Except is one of them. Unknown input value we will look for. We contemplate to the function «Selection parameters» in Excel in the example now.

It is necessary to choose the interest rate on the loan, if we know the amount of money and timefrime. Complete to the table by the input information.

input information.

The percentage rate is unknown, so the cell B3 is empty. To calculate the monthly payments using the =PLT() function.

When the conditions of the problem is recorded, proceed to the «DATA» tab. «Data Tools»-«What-If Analysis»-«Goal Seek».

What-If Analysis.

In the field «Set cell» we set the reference to the cell with the calculation formula (B4). The field «To value» is intended to introduce the desired result of the formula. In this example, this is amount of monthly payments. For example -5000 (that the formula to work properly, put the sign up «minus», because these money will be given to). In the field «By changing cell» – we set the absolute reference to the cell with the desired parameter ($B$3).

desired parameter.

After you click OK on the screen will appear the result.

will appear the result.

To save, press OK or ENTER.

To save.

The function «Goal Seek» changes the value in the cell B3 until it receives the result of user-defined formulas, which was written in the cell B4. The command gives only one solution of the problem.



The solution of the equation by the method Goal Seek in Excel

The «Goal Seek» function is perfect for solving equations with one unknown. We consider for example the expression: 20 * x - 20 /x = 25. The argument x – is the desired setting. Let this function helps to solve the equation of the parameter selection and display the value found in the cell E2.

In the cell E3 we enter the formula: = 20 * E2 - 20 / E2. And in the cell E2 we enter any number, that is in the domain of the function. Let it be 2.

Let it be 2.

Running the tool and fill in the fields:

  • «Set in the cell» - E3 (the cell with the formula);
  • «Value» - 25 (the result of the equation);
  • «By changing the value of the cell» - $ E $ 2 (the cell assigned to the argument x).
argument x.

The result of the function:

result of the function.

The found argument appears in the cell reserved for it.

The solution of the equation: x = 1.80.

The function «Goal Seek» returns search results as the first value found. Regardless of that, how the equation has the solutions.

If, for example, we put in the cell E2 we put the seed -2, the solution will be another.

for example.

The examples of selection the parameter in Excel

The function in Excel «Goal Seek» is used when the result of the formula is known, but the initial setting to get the result is unknown. To select the input values, is used the built-in command.

The example 1. The selection method of the initial investment amount (of contribution).

The known parameters:

  • the term - 10 years;
  • the profitability - 10%;
  • the compounding ratio – the calculated value;
  • the sum payment at the end of the period – is the desired number (500 000).

We insert the input dates into the table:

dates into the table.

The seed money – is the unknown value. In the cell B4 (the compounding ratio) – is the formula = (1 + B3) ^ B2.

Call the «Goal Seek» command window. Fill in the fields:

Fill in the fields.

After the command Excel outputs the result:

outputs the result.

To 10 years to get 500 000 at 10% per annum, it is required to make 193 050.2

The example 2: Calculate the possible increase in old-age pension due to the participation in the state program of co-financing.

The input dates:

  • the monthly payments - 1000;
  • the period of payment of additional premiums – is the calculated value of (the retirement age (in this example - for a man) minus the age of the participant of the program at the time of his accession);
  • the pension savings – the estimated value (accumulated amount in the period by a member, the state increased in 2 times);
  • the expected period of payment of the retirement pension - 228 months;
  • the desired increase to the pension - 2000.
monthly payments.

From what age you should pay 1000 in additional insurance premiums to get an increase to the pension in 2000:

  1. The cell with the formula for calculating pension increases is active - call the command «Goal Seek». Fill in the fields in the open menu.
    2. Goal Seek.
  2. Press OK - get the selection result.
selection result.

To get an increase in 2000, you need monthly transfer to the funded part of the pension of 1000 with 41 years.

The «Goal Seek» function works correctly, if:

  • the value of the desired result is expressed by the formula;
  • all formulas are written in full and without errors.

en ru