Excel What-if Analysis: Goal Seek

Ever find yourself wondering if Excel could help you answer the question: "What values would I need to get a known result?" The What-if Analysis features, available in Excel 2010 and up, give you the tools to do just that. Unlike functions and formulas that take given values and return a result, what-if scenarios allow you to explore the results of multiple variables and Goal Seek lets you specify the result and then determines the values needed to generate it.

You can learn about What-if Scenarios here. In this article, we'll look at Excel's Goal Seek feature.

What is Goal Seek?

Goal Seek is useful when you know the answer you want from a formula, but aren't sure what value is needed to get that result. In the What-if Scenarios article, we looked at how to compare the monthly payment and total interest for loans at different interest rates and terms. But what if, instead, you knew how much you wanted to pay per month and needed to know how long it would take you to pay off the loan. Goal Seek is your solution. To follow using our example, download ExcelGoalSeek.xlsx

This feature was introduced in Excel 2010 and applies to 2010 and later. Images were taken using Excel 2013 on Windows 7.

How to Use Goal Seek

1. Set up your table to include all known data and the formulas needed to calculate the results. In our example, we use the PMT function in B8 to calculate the monthly payments based on our other known factors (Interest Rate and Loan Amount) and our unknown value (Term in Months).Put any value in the unknown cell to test your formulas.

1. On the Data tab, select Goal Seek from the What-if Analysis dropdown menu.
2.  In the Goal Seek dialog box, fill out the criteria for your search:

• Set cell: Select the cell that contains your results formula. (The cell must contain a formula for Goal Seek to work!)
• To value: Type the amount you want the formula to return. In this case, we would like a monthly payment of $600.
• By changing cell: Select the cell of your unknown variable. (This cell must not contain a formula.)

Note: In Excel 2016, What-if Analysis appears in the new Forecast Group.

1. Click The dialog box will indicate that it is searching for a solution that meets your criteria. If it does, you will see the Goal Seek Status dialog box, and your table will be updated to show the solution.

Notice that the result may not be an even number like you might want in this case. It is also possible that Excel is unable to find a result if the criteria is not solvable. Even so, Goal Seek is a useful tool for "working backwards" to figure out values in a complicated equation without having to "guess and try again".

Hot Tip: Goal Seek With Multiple Variables

Goal Seek will only work with one result and one variable. If you wish to find a specific answer with multiple variables and constraints, you will have to use the Solver add-in. To access Solver, go to the Options window from the File tab. Click Add-Ins. Select Solver Add-in in the Add-ins pane, then click OK.

The solver feature will now appear in the Data tab.