An Ultimate Guide To Learn Goal Seek in Excel

What-If-Analysis in Excel

What-if analysis is the way of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the different results.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

Goal Seek Example 1

In this example, we aim to use goal seek to find the marks needed to be scored in chemistry so that the overall grade reaches 90.

• Enter the formula in the cell adjacent to the final grade to calculate the average of all the marks.

Go to Data > What - If Analysis > Goal Seek

• The marks in the Chemistry subject is our input cell. You have to set the value of the final grade to 90 by changing the marks of Chemistry.

• Click OK. You can see the marks for Chemistry are automatically filled so that the final grade reaches 90.

Goal Seek Example 2

In this example, our aim is to find what will be the rate of interest if the person wants to pay 

$3000 per month to settle the loan amount.

PMT function is used to calculate the monthly payment amount.

Let’s go step-by-step to see how we can find the rate of interest that will settle a loan of $200,000 by paying $3,000 a month.

• Enter the PMT formula in the cell adjacent to the Payment cell. Since there is no value currently in the rate of interest cell, Excel assumes it will be 0% which gives us the payment of $1,333.33. Ignore it.

• Follow the steps discussed in the previous example to open the goal seek argument window. We will set the monthly payment to -3,000. Note that it’s a negative value since the amount will be deducted.
  
  Set rate of interest as the changing cell.

Your Data Analytics Career is Around The Corner!

Data Analyst Master’s ProgramExplore Program

• Click OK. You will see the goal seek function gives the interest rate needed to pay the loan amount in 150 months, paying $3,000 a month.

Go to Home > Number and change the value to Percentage.

Your final outcome will look like below:

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

In this article, you have learned the goal seek function in MS Excel. They can be used to perform many operations that are otherwise time-consuming.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you!