Looking for:
Excel in Depth - MrExcel Products - MrExcel Publishing |
Excel In Depth: Jelen, Bill: : Books.
One of the most popular group of features in Excel are the What-If Analysis tools. 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:. 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. 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 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 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 Now, let's assume further that Lisa Stephens needs to achieve a Final score of at least 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 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 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 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. As explained at office. 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. 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 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 Once you've completed the 3 steps above or entered the keyboard shortcut Excel displays 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 :. 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.
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 At this point, you can click on either of 2 buttons at the bottom of the dialog box:.
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…. The following are some common problems or challenges that you may encounter from time-to-time when using the Excel Goal Seek tool:.
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:. 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. 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…. 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 in Depth , this basically means that Excel plugs in different values in the Changing Cell until it finds a value that solves the problem.
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 :.
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. 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. 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. You can increase the maximum number of iterations that Excel undertakes in the following 4 easy steps :. If you're like me and prefer using keyboard shortcuts , you can replace the steps above by the following 3 simple steps:. 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.
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 :.
If you want to use keyboard shortcuts , you can change the Maximum Change between iterations in the following 3 easy steps:. In the Excel 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 Bible , is that:.
Comments
Post a Comment