Excel Goal Seek

Goal Seek and other essential Excel tools are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Goal Seek

You can use Goal Seek as a powerful calculator in Excel where it will calculate part of an equation.

Equation 1 

If we step back for a little bit, let's go back to algebra. In algebra, we have an algebraic expression that sometimes includes the value of X. Now, the value of X is one of the precedents to the result that we get in the equation. 

So we get 7 here in this algebraic expression, but we don't know the value of X. So the term we use is we need to solve for X. 

If you take a look at this long enough, you can probably determine that X is equal to 5, because the relationship between 7 and 2 is that if you subtract 2 from 7, you'll figure out and get what the answer is for X. 

So that is a little bit of algebra there, and that's easy to do if you're just looking at this very simple equation. 

Equation 2 

Here's another equation. Someone has an IRA balance of $10,000. They have an annual growth rate of 5 percent. Over the course of 10 years, their total return on their $10,000 investment is $16,289. Now, they're not really happy with that. They would prefer to have reached $20,000. 

So here is what we would have to solve for in this case. We would refer to this as solving for X. Five percent is only going to get us to $16,289. So what does our growth rate need to be in order for us to get to $20,000 by Year 10? 

That's where we'll use Goal Seek. Goal Seek is available on the data tab. If you move over to the forecast group, and then you'll click the dropdown for the What If Analysis command and you'll see Goal Seek as one of the tools you can use for What If Analysis. I'll click Goal Seek. 

Now this is very similar to subtotals. You want to be able to create a sentence that makes sense for the result that you want. So the first thing you need to do is select the cell that you want to set to a certain value by changing another cell. 

So I want to set this cell to a value of 20000 by changing the value of the growth rate. Now, Goal Seek is going to do its best to calculate what the growth rate needs to be in order for me to get to $20,000. If it's able to come up with an answer, it will let me know. And it says, in order to get to my target value of $20,000, I need to have a growth rate of 7.2 percent. 

Exercise 

So in class, we would have you tackle this next exercise. 

Pretend you're tutoring someone, and they want to get a weighted average of 88, but currently, their final exam score looks like it's going to be 80. That is not enough to get them to a weighted average of 88. 

So using Goal Seek, again you'll go to the forecast group, and then click the dropdown and choose Goal Seek. You'll fill in the appropriate variables. So what I'm looking to do here is set the weighted average to a value of 88 by changing my final exam score or the final exam score of maybe the person I'm tutoring. When I click OK, Excel lets me know that this person would need to get a 91 on their final exam score in order for them to get a weighted average of 88. 

Recap 

And that's how you can use Goal Seek as a forecasting tool and figure out the value of X, the unknown variable that's going to get you to your goal.