How to use Goal Seek in Excel (Easy Tutorial)

Goal Seek Example 1 | Goal Seek Example 2 | Goal Seek Precision | More about Goal Seek
If you know the consequence you want from a rule, practice Goal Seek in Excel to find the input value that produces this convention solution .

Goal Seek Example 1

Use Goal Seek in Excel to find the grad on the fourthly examination that produces a final grade of 70 .
1. The recipe in cell B7 calculates the final grade.

Formula Result

2. The grade on the fourth examination in cell B5 is the input signal cell .
Input Cell
3. On the Data tab key, in the Forecast group, click What-If psychoanalysis .
Click What-If Analysis
4. Click Goal Seek .
Click Goal Seek
The Goal Seek dialogue box appears .
5. Select cell B7 .
6. Click in the ‘To rate ‘ box and type 70 .
7. Click in the ‘By changing cell ‘ box and choice cellular telephone B5 .
8. Click OK .
Goal Seek Parameters
Result. A rate of 90 on the one-fourth examination produces a final grade of 70 .
Goal Seek Solution

Goal Seek Example 2

Use Goal Seek in Excel to find the loan amount that produces a monthly payment of $ 1500 .
1. The rule in cell B5 calculates the monthly requital .
PMT function
explanation : the PMT affair calculates the payment for a lend. If you ‘ve never heard of this function before, that ‘s OK. The higher the loanword sum, the higher the monthly payment. Assume, you can lone afford $ 1500 a calendar month. What is your maximum loan amount ?
2. The loan measure in cell B3 is the remark cell .
Loan Amount
3. On the Data tab key, in the Forecast group, cluck What-If analysis .
Click What-If Analysis
4. Click Goal Seek .
Click Goal Seek
The Goal Seek dialogue 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 ‘ corner and choose cell B3.

8. Click OK .
Goal Seek Variables
Result. A loan measure of $ 250,187 produces a monthly payment of $ 1500 .
Goal Seek Found a Solution

Goal Seek Precision

Goal try returns approximate solutions. You can change the iteration settings in Excel to find a more accurate solution .
1. The convention in cell B1 calculates the square of the value in cell A1 .
Formula Cell
2. Use finish search to find the remark value that produces a formula leave of 25 .
Goal Seek Example
Result. Excel returns an approximate solution .
Approximate Solution
3. On the File pill, cluck Options, Formulas .
4. Under calculation options, decrease the Maximum Change measure by inserting some zero. The default respect is 0.001 .
Maximum Change Value
5. Click OK .
6. Use Goal Seek again. Excel returns a more precise solution .
More Precise Solution

More about Goal Seek

There are many problems Goal Seek ca n’t solve. Goal Seek requires a single stimulation cell and a single output ( formula ) cellular telephone. Use the Solver in Excel to solve problems with multiple remark cells. sometimes you need to start with a different input signal respect to find a solution .
1. The formula in cell B1 below produces a consequence of -0.25 .
Formula
2. Use Goal Seek to find the input signal respect that produces a formula leave of +0.25 .
Goal Seek Problem
Result. Excel ca n’t find a solution .
Goal Seek Status
3. Click Cancel .
4. Start with an input value greater than 8 .
Input Value Greater Than 8
5. Use Goal Seek again. Excel finds a solution.

Goal Seeking in Excel
explanation : y = 1 / ( x – 8 ) is discontinuous at x = 8 ( dividing by 0 is not possible ). In this example, Goal seek can not reach one side of the x-axis ( adam > 8 ) if it starts on the other side of the x-axis ( adam < 8 ) or frailty versa .

informant : https://thaitrungkien.com
Category : Tutorial

Related Posts

Trả lời

Email của bạn sẽ không được hiển thị công khai.