How to Use Goal Seek in Excel (5 Examples)

What Is Goal Seek in Excel

Goal Seek is a feature of the What-If Analysis Command in Excel. This feature helps to find a specific input value for the desired output from a formula. You can solve many real-life problems by using this feature. This feature uses the trial and error method to back-calculate the input value.

Read More: How to Use Goal Seek to Find an Input Value

Parameters of Goal Seek

3 parameters in the Goal Seek dialog box must be specified to get the result. They are:

1. Set cell: In the Set cell parameter, you must select the cell containing the formula. This is the cell where you want to find the targeted value.

2. To value: In this parameter, you must enter the value you want as the formula’s output.

3. By changing cell: In this parameter, select the cell where you want to change or find the input value.

Example 1 – Solving a Mortgage Problem in Excel by Using Goal Seek

Steps:

• Go to the Data tab.
• Select What-If Analysis.

A drop-down menu will appear.

• Select Goal Seek from the drop-down menu.

A dialog box named Goal Seek will appear.

• Select the Set cell. Here, I selected cell C9 because it contains the payment formula.
• Enter To value. Here, I wrote -1000 because the maximum Payment is limited to $1’000. And, as you are making a Payment the sign is negative.
• Select By changing cell. Here, I selected cell C7 because this cell contains the Interest Rate.
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the current value.

• Select OK.

You can see that I have found the highest annual Interest Rate you can tolerate if your maximum monthly Payment limit is $1,000.

Example 2 – Using Goal Seek to Find Unknown Value in Excel

Steps:

• Select the cell where you want to calculate the Average Cost. Here, I selected cell C11.
• In cell C11, enter the following formula:

Here, the formula will subtract the value in cell C7 from 40 and then multiply it by the value in cell C9. Then, the value in cell C7 will be multiplied by the value in cell C7. Both of these values will now be summed to get the total cost. Then, the formula will divide the result by 40 and return the Average Cost.

• Press ENTER to get the result.

The Average Cost is not showing the right value as you have not entered any value in cell C7 yet. Here, I will explain how you can use the Goal Seek feature in Excel to find out the pounds of Candy A you need to buy to get an Average Cost of $8.

• Go to the Data tab.
• Select What-If Analysis.

A drop-down menu will appear.

• Select Goal Seek from the drop-down menu.

The Goal Seek dialog box will appear.

• Select the Set cell. I selected cell C11 because this cell contains the formula for the Average Cost.
• Enter To value. Here, I wrote 8 because the expected Average Cost is $8.
• Select By changing cell. I selected cell C7 because this cell contains Candy A (Pounds).
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the Currant value.

• Select OK.

You can see that I have found how many Pounds of Candy A you will need to buy to make the average cost $8.

Example 3 – Applying Goal Seek to Find Breakeven Point

Steps:

• Select the cell where you want to calculate the Profit.
• Enter the following formula in the selected cell:

Here, the value in cell C9 is multiplied by the value in cell C10. Then, the value in cell C5 and cell C6 is subtracted from the result. Then, the value in cell C7 is multiplied by the value in cell C8, which is subtracted from the previous result and returns the profit.

• Press ENTER to get the Profit.

The Profit is -$22,300 because you do not have any gross income yet as you have not entered the Number of non-speaker.

• Go to the Data tab.
• Select What-If Analysis.

A drop-down menu will appear.

• Select Goal Seek from the drop-down menu.

The Goal Seek dialog box will appear.

• Select the Set cell. I selected cell C12 because this cell contains the formula for the Profit.
• Enter To value. I wrote 0 because the Profit is 0 at the breakeven point.
• Select By changing cell. I selected cell C10 because this cell contains the Number of non-speakers.
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the Currant value.

• Select OK.

I got the Number of non-speakers needed for you to reach the breakeven point by using Goal Seek and, it is 25.

Example 4 – Solving an Equation in Excel by Using Goal Seek

Steps:

• Select the cell where you want the Solution of the equation. Here, I selected cell C7.
• In cell C7, enter the following formula:

Here, the formula will raise the value in cell C5, the x, to the power of 2. Then, multiply the value in cell C5 by 10 and then subtract it from the previous result. After that, it will add 25 with the result and return the Solution of the equation.

• Press ENTER to get the result.

The result is 25 as I have not entered any value for the Variable (x).

• Go to the Data tab.
• Select What-If Analysis.

A drop-down menu will appear.

• Select Goal Seek from the drop-down menu.

The Goal Seek dialog box will appear.

• Select the Set cell. Here, I selected cell C7 because this cell contains the formula for the Solution of the equation.
• Enter To value. I wrote 0 because the Solution is 0 in the given equation.
• Select By changing cell. I selected cell C5 because this cell contains the Variable (x).
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the Currant value.

• Select OK.

You can see that I have found the value of the Variable (x) by using the Goal Seek feature in Excel.

Example 5 – Using Goal Seek in Excel for Effective Interest Rate

Steps:

• Select the cell where you want to calculate the Effective Interest Rate. Here, I selected cell C8.
• In cell C8, enter the following formula:

Here, I divided the value in cell C5 by the value in cell C6 and then added 1 with the result. Then, the result is the power of the value in cell C6. After that, I subtracted 1 from the result, and the formula will return the Effective Interest Rate.

• Press ENTER to get the result.

I will use the Goal Seek feature to find the Compounding Periods Per Year needed for the Effective Interest Rate to be 10.2%.

• Go to the Data tab.
• Select What-If Analysis.

A drop-down menu will appear.

• Select Goal Seek from the drop-down menu.

The Goal Seek dialog box will appear.

• Select the Set cell. I selected cell C8 because this cell contains the formula for the Effective Interest Rate.
• Enter To value. I wrote .102 because the expected Effective Interest Rate is 10.2%.
• Select By changing cell. I selected cell C6 because this cell contains the Compounding Periods Per Year.
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the Current value. The Current Value does not match exactly with the Target Value, but it is close, and I am satisfied with it.

• Select OK.

I have found the Compounding Periods Per Year for the desired Effective Interest Rate.

How to Use Goal Seek in Excel For Multiple Cells

Steps:

• Select the cell where you want to calculate the Profit Percentage. Here, I selected cell E5.
• In cell E5, enter the following formula:

Here, I subtracted the value in cell C5 which is the Total Cost from the value in cell D5 which is the Selling Price. And, then divided the result by the value in cell C5. Then, I multiplied the result by 100% and now the formula will return the Profit Percentage.

• Press ENTER to get the result.

• Drag the Fill Handle to copy the formula to the other cells.

You can see that I have copied the formula and calculated the profit percentage for each product.

I will show you how to use Excel VBA to use Goal Seek for multiple cells. Here, I will find out the Selling Price for every Product if I want the Profit Percentage to be 35%.

• Go to the Developer tab.
• Select Visual Basic.

The Visual Basic editor window will open.

• Select the Insert tab.
• Select Module.

A module will open.

• Enter the following code in that module:

Sub Goal_Seek_Multi_Cells()
For row_no = 5 To 9
Worksheets("Goal Seek for Multiple Cells").Cells(row_no, "E").GoalSeek Goal:=0.35, _
ChangingCell:=Worksheets("Goal Seek for Multiple Cells").Cells(row_no, "D")
Next row_no
End Sub

• Save the code and go back to the worksheet.
• Go to the Developer tab.
• Select Macros.

A dialog box named Macro will appear.

• Select the Macro name.
• Select Run.

You will see the Selling Price of every product has changed accordingly to get a Profit Percentage of 35%.

Read More: How to Automate Goal Seek in Excel

Goal Seek Precision in Excel

Steps:

• Go to the File tab.

• Select Options.

A dialog box named Excel Options will appear.

• Go to the Formulas tab.
• Decrease the value for Maximum Change to get a more precise result.
• Select OK.

Go back to the worksheet.

• Go to the Data tab.
• Select What-If Analysis.
• Select Goal Seek.

The Goal Seek dialog box will appear.

• Select the Set cell. I selected cell C8 because this cell contains the formula for the Effective Interest Rate.
• Enter To value. I wrote .102 because the expected Effective Interest Rate is 10.2%.
• Select By changing cell. I selected cell C6 because this cell contains the Compounding Periods Per Year.
• Select OK.

Another dialog box named Goal Seek Status will appear. This dialog box displays the Target value and the Current value. Here, the Current Value matches the Target Value, and I have achieved a more precise result.

• Select OK.

You can see that I have found the Compounding Periods Per Year for my desired Effective Interest Rate.

Things to Remember

• It should be noted that the cell you are selecting for By changing the cell parameter must contain a value. If the cell contains a formula, then you will get an error.
• Whenever working with VBA, you must save the Excel file as an Excel Macro-Enabled Workbook. Otherwise, the macros won’t work.

Practice Section

I have provided a practice sheet for you to practice using Goal Seek in Excel.

Download the Practice Workbook

Related Article

• How to Do What-If Analysis Using Goal Seek in Excel

<< Go Back to Goal Seek in Excel | What-If Analysis in Excel | Learn Excel