How does goal seek work in excel 2010

This is the number of months that you want to pay off the loan. Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula.

Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek. This formula calculates the payment amount. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.

The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate. You can ignore that value for now. In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve.

In the example, this reference is cell B4. In the To value box, type the formula result that you want. In the example, this is Note that this number is negative because it represents a payment.

In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3. Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box. Click OK. Goal Seek runs and produces a result, as shown in the following illustration. Set cell: is the cell that contains the end result that we want to get. To value: this is the value that we want the Set cell to be equal to.

In this case, that would be. By changing cell: this is the cell that Goal Seek will change in order to get the desired value in the Set cell. This is cell B2 , the Selling Price cell. You click in the input fields and then select the desired cells on the spreadsheet, but you must type in the value for the To value input. In our example, it looks like this: Click OK and Excel will do its magic. The next window will give some information on the solution it found and you can just hit OK to go to the spreadsheet.

Notes In many simple cases, Goal Seek will work just fine, but, Goal Seek often can't find the exact answer for more complex problems; in that case, you will need to use Solver. Make sure to download the attached file to work with this example in Excel. Downloadable Files: Excel File You must sign-in to download the file. You can Signup for Free if you don't already have an account. Our Excel Courses. Professional Input Forms in Excel. Author: Don. Send Emails from Excel Multiple Methods.

Similar Content on TeachExcel. 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.