19.2. Using Goal Seek
There are some problems that formulas just can't crack. One of these is solving questions that need trial-and-error guesswork.
In most Excel spreadsheets, you begin with a set of data and use formulas to analyze that data and calculate some conclusions. When you create plans and projections, however, you sometimes invert this process and start with the conclusions. For example, you might begin with a profit projection and calculate "backward" to find out the sales you need to make. Or, you might calculate the rate an investment needs to increase in value in order to meet a set target.
Goal seeking is a fairly simple tool in Excel that can help you answer these sorts of questions, provided your problem meets the following guidelines:
There's only one result cell and this cell contains a formula . You want to make sure the result of this calculation meets a target you specify.
There's only one variable cell . This is the value that Excel adjusts to meet your target in the result cell.
There's a valid solution . For example, if you ask Excel how many years it'll take to pay off a $100,000 loan by making 25-cent monthly payments, you'll end up with an answera negative number of yearsthat makes no sense (since your loan is accumulating interest faster than you're making payments).
To better understand goal seeking, it helps to think about a simple example. Consider the worksheet shown in Figure 19-7, which shows a list of student grades. Grace Dewitt is given the chance to resubmit the assignment, and she wants to determine what grade she needs on the assignment to increase her final grade from 72 to 80 out of a possible 100.
To use goal seeking in this scenario, just follow these steps:
Choose Tools Goal Seek.
The Goal Seek dialog box appears (Figure 19-8).
In the "Set cell" text box, enter the cell you want Excel to optimize.
In the student grade example, this is cell E3, which contains Grace's final grade.
In the "To value" text box, enter your target.
Grace is seeking a final grade of 80 percent, so the target is 0.80 .
In the "By changing cell" text box, enter the cell that Excel should modify to achieve the target.
Cell D3, which contains the assignment grade, is the value that Excel modifies to determine what grade she needs to get on the assignment in order to boost her final grade to 80.
Click OK to start goal seeking.
The Goal Seek Status dialog box appears, and Excel cycles through a series of trial values, incrementing the assignment grade and calculating the resulting value.
If Excel can't find the answer after a series of attempts, the Goal Seek Status dialog box gives you the option to keep trying by clicking Step. If Excel stalls on a long analysis, you can stop it by clicking Pause, although this move is rarely necessary. In most cases, the goal-seeking process finishes before you can even take your hand off the mouse to reach for a nearby coffee mug.
In a short amount of time, Excel discovers that an assignment grade of 91 (out of 100) raises the final grade to 80 percent. At this point, it stops the goal seeking process and shows you the answer.
When an answer appears, click OK to accept the change and return to your worksheet, or click Cancel to return to the original version of your data.
Figure 19-9 shows the adjusted worksheet.
In the student grade example, you don't necessarily need to use goal seeking to get the answer you're looking for. You could get the same information by rewriting the equation. To understand how this approach works, it helps to take a closer look at the formula that calculates the final grade:
=(B3/B12)*25% + (C3/C12)*25% + (D3/D12)*50%
In this formula, the two tests are each worth 25 percent of the final grade, and the assignment is worth the remaining 50 percent. However, using a dash of high school math, you can rearrange this formula to find what grade you need on the assignment to get an 80 percent overall grade ( assuming you already know your grades on the two tests). Here's the answer:
=(80% - (B3/B12)*25% - (C3/C12)*25%)/50%*D12
This formula looks a little intimidating at first glance, but it's really not too difficult to understand. First of all, you start with the 80% final grade you're trying to achieve. You subtract the two test scores to find the value you need from the assignment. Finally, you divide that value by 50 percent (the weighting of the assignment) and multiply it by D12 (the total score available in the assignment). The end result is 91.25.
Based on this example, you might assume that goal seeking is just a tool for quickly calculating a number that you could obtain on your own by rewriting one or more formulas. Although that's often the case, there are some types of formulas that you can't reorganize to provide an answer. These formulas often have exponents and use the value in the variable cell more than once. You can find many examples of this sort of equation in the scientific world.
One common example is the quadratic function , which shows up in lots of different scientific modeling and engineering formulas. A typical quadratic formula looks like this:
y = x 2 + 5x + 10
In this case, it's easy to calculate the result (y) provided you know the input value (x). However, it's much more difficult to perform the reverse taskdetermining x if all you know is y. Try as you might, there's no way to rearrange the formula to solve the problem. In this case, goal seeking becomes very useful. It uses a trial-and-error approach that can usually get the answer you need. Excel tries different values of x in succession trying to get closer and closer to the target result.
To put the problem into Excel's formula format, assume that cell B1 contains the x value. Here's what the formula would look like:
=B1^2 + 5*B1 + 10
Figure 19-10 shows an example worksheet that puts this function to the test. Clearly, it's easy to see that an input value of 20 produces a result of 510. But what input value do you need to get a result of 1000? In this example, goal seeking is the only easy way to find the answer (which, incidentally, is 29.0643).