One of the most popular group of features in Excel are the What-If Analysis tools. In Microsoft's words, What-If Analysis tools allow you to “try out various values for the formulas in your sheet“.
This particular Excel tutorial focuses on 1 of these What-If Analysis tools: Goal Seek.
Learning how to use Goal Seek is extremely helpful because, as I explain below, you can use Goal Seek whenever you know the resulting value that you want a particular formula to return but aren't sure what is the precise input that is required to achieve that result.
My purpose with this Excel tutorial is to provide all the information you need to start using Goal Seek now. Therefore, in addition to introducing and explaining how you can use Goal Seek, I show how you can deal with some common challenges you may face when working with this feature.
Additionally, since one of my main focuses at Power Spreadsheets is Visual Basic for Applications, the second part of this blog post explains how you can use Goal Seek (and deal with some of the challenges arising in connection with it) with VBA.
As with all other Excel and VBA tutorials in Power Spreadsheets, this blog posts includes a detailed practical example that shows (step-by-step) how to implement everything.
You can use the following table of contents to navigate to the section that interests you the most:
Table of Contents
Before we dive into Goal Seek, let's start by taking a look at the…
Example For This Tutorial
This Excel VBA Goal Seek Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
In this particular example, we'll be taking a look at the exam scores obtained by a certain University student named Lisa Stephens.
Let's assume that, on this semester, Lisa Stephens is taking a class that is graded through 3 exams. Each of the exams has exactly the same weight. Therefore, her final score is determined by averaging her scores in those 3 exams.
In order to use Excel to determine Lisa's final score, you can set up a formula that uses the AVERAGE function. Such a formula calculates Lisa's final score automatically once you enter her individual scores in each of the 3 exams.
Let's assume that Lisa Stephens has already taken 2 out of the 3 exams. Her scores in these 2 exams where 77.0% and 57.0%.
Now, let's assume that you want to carry out some scenario analysis.
One approach you can take is to enter different scores for Exam 3. Excel automatically calculates Lisa's Final score. For example, if I enter 87.0%, Excel determines that Lisa's Final score is 73.7%.
In such a case, the question you're asking to Excel is: what is Lisa Stephens' Final score if her score in Exam 3 is 87.0%?
Now, let's assume further that Lisa Stephens needs to achieve a Final score of at least 75.0%. In such a situation, you may be interested in knowing what is the minimum score Lisa must get in Exam 3 for purposes of achieving a Final score of 75.0%.
Theoretically, you can continue with the same approach. You can plug different values in cell E6 (the score for Exam 3) until, eventually, you plug in the score that results in Lisa's Final score being 75.0%.
This may not be the most efficient way to proceed.
What I mean is that, in such situations, you may want to take the opposite approach. This means that you may want to ask Excel a different question. Instead of asking what is Lisa's Final score if her score in Exam 3 is #, you can ask: what is the score that Lisa Stephens must achieve in Exam 3 in order to get a Final score of 75.0%?
In the following sections, I explain how you can answer this question, and those that have a similar structure, by using the Range.GoalSeek VBA method.
Let's start by taking a look at…
Excel Goal Seek
As explained at office.com:
If you know the result you want from a formula, but you aren't sure which input value the formula needs to get that result, use the Goal Seek feature.
In other words, you can use the Excel Goal Seek tool for purposes of determining the value that you must enter in a particular cell (the Changing Cell) to get the result you want (the Goal) in another cell that depends on the Changing Cell. To a certain extent, Goal Seek is the (rough) opposite of worksheet formulas.
Goal Seek uses a single variable input value. This means that there's only one Changing Cell.
If you're working with more than one input (Changing) cell, the appropriate tool is usually Solver. I may cover Solver in a future tutorial. If you want to be notified by email every time that I publish new content in Power Spreadsheets, please register for our newsletter by entering your email address below.
In the words of Excel guru John Walkenbach (in the Excel 2016 Bible):
Single-cell goal seeking is a rather simple concept.
Therefore, let's go back to the practical example I introduce above. The question we want Excel to help us answer is as follows:
What is the score that Lisa Stephens must achieve in Exam 3 in order to get a Final score of 75.0%?
At this point, the relevant table in the Excel worksheet we're working with looks as follows:
And the question you're asking looks something like this:
You can get Excel to provide you the value that should go in cell E6 (score in Exam 3) so that the value in cell F6 (Final score) is equal to 75.0% by following these 3 easy steps:
Step #1: Display The Goal Seek Dialog Box
To open the Goal Seek dialog box, proceed as follows:
- Step #1: Go to the Data tab of the Ribbon.
- Step #2: Click on “What-If Analysis”.
- Step #3: Once Excel displays the drop-down, click on “Goal Seek…”.
Alternatively, you can use the keyboard shortcuts “Alt + A + W + G” or “Alt + T + G”.
Once you've completed the 3 steps above (or entered the keyboard shortcut) Excel displays the Goal Seek dialog box.
Step #2: Provide The Relevant Information In The Goal Seek Dialog Box
As you can see in the dialog box above, the Goal Seek dialog box asks you for the following 3 inputs:
- Input #1: Set cell.
This is the dependent cell that holds the formula for which you want to seek a Goal. In this input field, you must specify a reference to a single cell that holds a formula.
- Input #2: To value.
This is the value that you want the formula in the dependent cell (which you specify in input #1 above) to return. In this input field, you must enter a hard-coded value. - Input #3: By changing cell.
This is the Changing Cell. In other words, this is the cell whose value Excel must adjust so that the formula in the dependent cell (specified in input #1) returns the value you want (specified in input #2 above).
In the example we're looking at, the relevant inputs are as follows:
- Input #1 (Set cell) is a reference to cell F6. This cell holds the formula that uses the AVERAGE function to calculate Lisa Stephens' Final score.
- Input #2 (To value) is the value 75%. This is the Final score that Lisa must achieve, as explained above.
- Input #3 (By changing cell) is a reference to cell E6. This is the cell where the Exam 3 score goes.
Once you've provided the relevant input in the Goal Seek dialog box, you can click the OK button on the lower section of the dialog box.
Step #3: Replace Value In Changing Cell Or Restore It
After you've completed step #2 above, Excel displays the Goal Seek Status dialog box.
Excel uses this dialog box to inform you about the following details:
- #1: Whether Excel has been able (or not) to find a solution.
- #2: What is the target value that you entered in the Goal Seek dialog box (input #2 in the previous step #2).
- #3: What is the current value of the dependent cell for which you're seeking a Goal (input #1 in the previous step #2).
In the example we're using in this tutorial, this dialog box looks as follows once the goal seeking process is completed. Notice the 3 pieces of data I mention above:
Excel also displays the results it has found in the worksheet. Notice how, in the following screenshot, Excel shows that Lisa Stephens must achieve a score of 91.0% in the third exam in order to have a Final score of 75.0%.
At this point, you can click on either of 2 buttons at the bottom of the dialog box:
- OK: If you click on OK, Excel replaces the value in the Changing Cell (input #3 in step #2 above) with the value it has found through the Goal Seek process.
As I mention above, in the example we're looking at, Excel sets the value for the Exam 3 score to be 91.0%. This is the minimum score that Lisa Stephens must achieve in order to obtain a Final score of at least 75.0%. - Cancel: If you click the Cancel button, Excel restores the worksheet to the same state it had before you launched the Goal Seek dialog box in step #1 above.
Before we jump onto the section where I explain how you can do all of the above by using Visual Basic for Applications, let's take a look at some…
Common Goal Seek Problems
The following are some common problems or challenges that you may encounter from time-to-time when using the Excel Goal Seek tool:
Problem #1: Goal Seek May Not Have Found A Solution
In some situations, the Goal Seek Status dialog box may inform you that Goal Seeking “may not have found a solution”.
In such situations, the value that Goal Seek sets for the Changing Cell (E6 in the screenshot above) doesn't (usually) make sense.
Broadly speaking, you can usually classify the reasons why this happens in the following 2 groups:
Reason #1: There Is No Solution To The Goal Seek Problem
As a general rule, the cells that you specify in both (i) the Set cell and (ii) the By changing cell input fields must have a precedent-dependent relationship. More precisely, the cell you specify in the Set cell input field must be a dependent of the Changing Cell.
Excel worksheets can be very complex. In certain circumstances, it may be difficult to ensure that there's a dependent-precedent relationship between the 2 cells that you specify in the Goal Seek dialog box.
Therefore, if Excel tells you that Goal Seeking may not have found a solution, one of the first things you should check is the logic of the worksheet. In other words, make sure that
- #1: There is a precedent-dependent relationship between the cells that you specify as input in the Goal Seek dialog box.
- #2: The Goal Seek problem actually has a solution.
Despite the above, there are situations where there's a solution but Excel seems to be unable to find it. These cases may fall under…
Reason #2: Excel Isn't Carrying Out Enough Iterations To Find A Solution
When you use the Goal Seek tool, Excel follows an iterative process for purposes of finding a solution. As explained by Excel authority Bill Jelen (Mr. Excel) in Excel 2016 in Depth, this basically means that Excel plugs in different values in the Changing Cell until it finds a value that solves the problem.
The following GIF shows how the Goal Seek Status dialog box looks like while Excel carries out the Goal Seeking process for this tutorial's example. I've adjusted the playback speed to create the slow motion effect and make the process easier to see. Notice how the contents of the Goal Seek Status dialog box change as the process advances:
Excel has a particular setting that sets the maximum number of iterations that it makes when an iterative calculation is undertaken. This maximum number of iterations applies to the Goal Seek tool.
Therefore, if Excel doesn't find a solution after undertaking that maximum number of iterations, it stops trying. In such cases, the Goal Seek Status dialog box informs you that Goal Seeking may not have found a solution.
In most situations, you can usually handle this challenge by implementing 1 or both of the following solutions:
Solution #1: Enter A Value In The Changing Cell That Is Closer To The Solution.
You might think this suggestion is crazy.
After all, you want Excel to tell you what is the value that should go in the Changing Cell. So:
Why would I suggest entering a value in that cell?
The logic behind this suggestion is that, if you enter a value that is closer to the solution in the Changing Cell, the number of iterations that Excel must undertake before finding a solution usually decreases. This is the case because Excel uses the current value in the Changing Cell as the base from which the iterative process begins.
Therefore, as a general rule:
The farther away the current value in the Changing Cell is from the solution, the higher the number of iterations needed to find a solution.
To see how this works in practice, let's go back to Lisa Stephens' test scores. Compare the following 2 GIFs and notice the difference in speed and number of iterations required. In order to make the whole process easier to follow, I've adjusted the motion speed of both GIFs.
- GIF #1: In this case, I leave cell E6 (Exam 3 score) empty.
- GIF #2: In this case, I set the initial value of cell E6 to be equal to 70.0% prior to carrying out the Goal Seek.
Solution #2: Increase The Maximum Number Of Iterations That Excel Undertakes.
If you implement it properly, solution #1 reduces the number of iterations that Excel requires to find a solution.
This solution #2 attacks the problem from a different perspective. It increases the number of iterations carried out by Excel before it “gives-up” and tells you that Goal Seeking may not have found a solution.
You can increase the maximum number of iterations that Excel undertakes in the following 4 easy steps:
- Step #1: Click on the File tab of the Ribbon.
- Step #2: Once you complete step #1, Excel takes you to the Backstage View. Here, click on “Options” on the lower section of the sidebar that appears on the left side of the screen.
- Step #3: Once you've completed the previous steps, Excel displays the Excel Options dialog. Within the Excel Options dialog box, select the Formulas tab on the left sidebar.
- Step #4: Change the maximum number of iterations by setting the value of the Maximum Iterations input field and click the OK button on the lower right corner of the Excel Options dialog box to confirm the changes.
If you're like me and prefer using keyboard shortcuts, you can replace the steps above by the following 3 simple steps:
- Step #1: Use the keyboard shortcuts “Alt + T + O + F + (Alt + X)” or “Alt + F + T + F + (Alt + X)”. These keyboard shortcuts take you directly to the input field for Maximum Iterations.
- Step #2: Enter the new value for Maximum Iterations.
- Step #3: Press the Enter key to confirm your choice,
Problem #2: Goal Seek Isn't Precise Enough
As explained in the Excel 2016 Bible:
Like all computer programs, Excel has limited precision.
In some circumstances, you may find that Excel returns a solution that is too far from the value that you're expecting (or looking for).
The cause of this is, usually, related to the iterative process used by Goal Seek. The reason for this is that, in addition to having a setting for the maximum number of iterations that Excel undertakes (which I explain above), Excel also has a setting that controls the maximum change between iterations. This setting also applies to the Goal Seek tool.
The Maximum Change between iterations setting determines when Excel considers that Goal Seeking has found a solution. More precisely, once the difference between (i) the current value of the dependent cell you're working with (specified in the Set cell input field of the Goal Seek dialog) and (ii) the target value (specified in the To value input field of the Goal Seek dialog) is smaller than the Maximum Change between iterations, Excel considers that it has found a solution.
Mathematically, Excel considers it has found a solution when the following condition is met:
| Set cell – To value | < Maximum Change between iterations
Once this condition is met, the Goal Seek tool provides you with the current value as a solution to the problem you've set up.
Assuming that you don't want to change the input you've provided in the Set cell and To value input fields of the Goal Seek value, the item you must focus on is the Maximum Change between iterations.
More precisely, you'll want to make the Maximum Change between iterations smaller. This forces the Goal Seek feature to provide a more accurate solution.
You can decrease the Maximum Change between iterations by following 4 simple steps:
- Step #1: Click on the File tab.
- Step #2: Click on “Options” on the lower left side of the Backstage.
- Step #3: Select the Formulas tab within the Excel Options dialog box.
- Step #4: Change (decrease) the value for Maximum Change and click on the OK button on the lower right corner of the dialog.
If you want to use keyboard shortcuts, you can change the Maximum Change between iterations in the following 3 easy steps:
- Step #1: Use the keyboard shortcuts “Alt + T + O + F + (Alt + C)” or “Alt + F + T + F + (Alt + C)” to get to the Maximum Change input field in the Excel Options dialog.
- Step #2: Enter a new value for Maximum Change.
- Step #3: Press the Enter key.
Problem #3: The Goal Seeking Problem Has Multiple Solutions
Some Goal Seek problems may have (theoretically) multiple solutions.
In the Excel 2016 Bible, John Walkenbach provides a typical example of such situation: problems involving square numbers or square roots. The reason why such problems may have multiple solutions is because any positive real number has 2 square roots. One of these square roots is positive and the other is negative.
When facing such situations, you may want to refer to my explanation above regarding how the iterative process used by the Goal Seek tool works. Of particular importance is the fact that Excel uses the current value of the Changing Cell as the base from which the iterations begin.
The consequence of this, as explained by John Walkenbach in the Excel 2016 Bible, is that:
If you use goal seeking when multiple solutions are possible, Excel gives you the solution that is closest to the current value.
Therefore, in such cases, you may want enter a value in the Changing Cell input field that is closer to the solution.
Problem #4: Goal Seek Slows Down Excel
Each Goal Seek iteration triggers the recalculation of all open workbooks.
This is something you may want to consider as you structure the Excel workbooks you use Goal Seek on. Generally, it's preferable to have a single workbook (fast and small) open whenever you're using the Goal Seeking tool.
Theoretically, you may be able to partially address this problem by adjusting Excel's iteration settings. However, before modifying these settings, bear in mind the following:
- Reducing the maximum number of iterations that Excel can make may result in Excel not being able to find a solution, as I explain above.
- Increasing the maximum change between each iteration may result in Excel returning inaccurate or imprecise values, as I show above.
After reading the previous sections, you probably have a very good grasp of Excel's Goal Seek feature. The following sections show how you can use Visual Basic for Applications to work with Goal Seek and specify several of the settings that I mention above.
Let's start by taking a look at the most basic VBA construct you'll be working with, the…
Range.GoalSeek Method
The main purpose of the Range.GoalSeek method is to calculate the value that is necessary to achieve a particular goal. You generally use the GoalSeek method to calculate the value that, when supplied to a particular formula, “causes the formula to return the number you want”.
The basic syntax of the Range.GoalSeek method is as follows:
expression.GoalSeek(Goal, ChangingCell)
The following are the main items of this syntax:
- expression: Placeholder for a Range object. When you're working with the GoalSeek method, this Range object must be composed of a single cell.
- Goal: The value that you want Excel to return in the cell that you identify within the expression item above.
In other words, Goal is (as implied by its name) the goal you're trying to achieve. - ChangingCell: The (single) cell that Excel must change in order to achieve the Goal.
Range.GoalSeek Method Macro Example
Let's go back to the sample Goal Seek problem that I introduce and solve manually above.
The following image shows the data we're working with. As a reminder, our purpose is to find what is the score that Lisa Stephens must get in Exam 3 in order to achieve a Final score (average of the scores in the 3 exams) of 75.0%.
The following simple macro (Goal_Seek_Sample) uses Goal Seek to find this value:
This particular VBA Sub procedure is very simple. However, in order to make everything extremely clear, let's take a look at each line of code.
Note that, strictly speaking, the macro has a single statement. I divide it in 3 separate lines using the line-continuation character ( _) to improve readability.
Line #1: Range(“F6”).GoalSeek
This line of VBA code calls the Range.GoalSeek method. In particular, notice how this line follows the basic syntax of the GoalSeek method that I explain above.
In this example, the Range object the GoalSeek method works with is cell F6 (Range(“F6”)) of the active worksheet. The single-cell Range object that you specify here is the one that, when using Goal Seek manually, goes in the Set cell input field of the Goal Seek dialog.
As I explain here, this simplified object reference (Range(“F6”)) assumes that you're working with (i) the Application object, (ii) the active workbook, and (iii) the active worksheet.
Line #2: Goal:=0.75
This line sets the value of the Goal parameter of the GoalSeek method.
In the example we're working with, this value is equal to 0.75 (75.0%).
The value that you set for this argument is the value you enter in the To value input field of the Goal Seek dialog (if carrying out the Goal Seek manually).
Line #3: ChangingCell:=Range(“E6”)
This line sets the ChangingCell parameter of the Range.GoalSeek method.
In the example we're looking at, the specified one-cell Range object is cell E6 of the active worksheet.
This argument is the equivalent of the one you enter in the By changing cell input field of the Goal Seek dialog.
The following screenshot shows the results I obtain when executing the sample Goal_Seek_Sample macro on the data within the workbook that accompanies this tutorial.
Notice that the results are exactly the same as those obtained manually above. In other words, the sample Goal_Seek_Sample Sub procedure achieves the same goal.
Now that you have seen how to use the GoalSeek method in Excel, let's take a look at…
How To Modify Iteration Settings With VBA: Application.MaxIterations And Application.MaxChange Properties
In the section about common Goal Seek problems above, I provide some examples of challenges that you may encounter when using Goal Seek. As I explain then, there are 2 Excel settings that determine how Excel carries out the iterative goal seeking process:
- Maximum Iterations.
- Maximum Change.
You can also manipulate both of these settings with Visual Basic for Applications. Therefore, let's take a look at the relevant VBA properties:
Application.MaxIterations Property
The Application.MaxIterations property is a read/write property. Its main purpose is as follows:
- If you're reading the property, it returns the maximum number of iterations that Excel can use.
- If you're setting the property, you use it to set the maximum number of iterations that Excel can use.
The basic syntax of MaxIterations is as follows:
expression.MaxIterations
“expression”, within the above syntax, is a variable representing an Application object.
Application.MaxChange Property
Just as the Application.MaxIterations property, the Application.MaxChange property is read/write. Its main purpose is as follows:
- If you're fetching the property's value, it returns the maximum amount of change between iterations.
- If you're writing the property, it sets the maximum amount of change between each iteration.
The basic syntax of the MaxChange property is substantially the same as that of MaxIterations. More precisely, the syntax of Application.MaxChange is:
expression.MaxChange
For these purposes, “expression” is a variable representing an Application object.
The sample Goal Seek problem that I use throughout this Excel tutorial is relatively simple. Therefore, I don't encounter any of the challenges that sometimes arise due to Excel's iteration settings.
However, to the extent that you may eventually have to deal with such situations, let's take a look at a practical VBA code example to modify such settings:
Sample VBA Code To Modify Iteration Settings
The following sample macro (Goal_Seek_Iteration_Sample) adjusts Excel's iteration settings prior to solving the sample Goal Seek problem that we've looked at throughout this blog post:
Notice that the second part of the Sub procedure is simply the statement that I explain above. This statement uses the Range.GoalSeek method to solve the sample problem.
Since I explain these 3 lines of code in a previous section, lets focus only on the first 4 lines of VBA code. These are the ones that deal with Excel's iteration settings.
Lines #1 And #4: With Application | End With
These 2 lines open and close (respectively) a With… End With block.
The effect of using the With statement is that the series of statements within the block (lines #2 and #3 below) are executed on a single object. The single object is specified in the opening statement. In this case, that object is the Application object.
In other words, the effect of the With… End With block is that lines #2 and #3 below work on the Microsoft Excel application (the Application object).
Line #2: .MaxIterations = 100
This line of code uses the Application.MaxIterations property to set the maximum number of iterations that Excel can use to be 100. You can, obviously, use a different value depending on your purposes.
I explain how to modify this setting manually above. As shown there, using the Application.MaxIterations property is the equivalent of setting the value of Maximum Iterations within the Formulas tab of the Excel Options dialog box.
Line #3: .MaxChange = 0.001
Similar to line #2, this line of code sets an iteration-related property. In this case, the Application.MaxChange property is used to set the maximum amount of change between each iteration to be 0.001. You can set a different value if required to achieve your goals.
Using the Application.MaxChange property for these purposes is the equivalent of setting the Maximum Change value within the Formulas tab of the Excel Options dialog. I explain how to do this manually in a previous section of this Excel tutorial.
As you'd expect, executing the Goal_Seek_Iteration_Sample macro on the workbook that accompanies this tutorial achieves exactly the same result as that obtained when executing the Goal_Seek_Sample example macro above.
However, by using the sample VBA code that I provide above, you should be able to easily adjust Excel's iteration settings in other situations where you're dealing with more complex Goal Seeking problems.
Conclusion
After reading this Excel tutorial on Goal Seek, you probably have a very good idea of how the Goal Seek tool can help you achieve your goals. More precisely, you've read about:
- What is Goal Seek.
- How to use Goal Seek, both manually and with Visual Basic for Applications.
- What are some of the most common problems and challenges that you may encounter while using Goal Seek.
- How you can deal with these challenges, both manually and using VBA.
This Excel VBA Goal Seek Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples above. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Books Referenced In This Excel Tutorial
- Jelen, Bill (2015). Excel 2016 in Depth. United States of America: Pearson Education Inc.
- Walkenbach, John (2015). Excel 2016 Bible. Indianapolis, IN: John Wiley & Sons Inc.