I’ve introduced many friends, colleagues, and students to the Goal Seek feature in Excel, and I usually get a similar reply: “Woah – how cool!” No matter the person, most everyone is surprised that they hadn’t heard of the tool before, and lament the fact that they hadn’t known about it sooner.
So, dear reader, I am excited to introduce you too! Here is an introduction to the Goal Seek tool with step-by-step instructions.
What is Goal Seek?
For one, Goal Seek is about simplification. It takes a complicated algebra problem and solves it for you, not only simplifying the equation, but in turn simplifying your life!
Why use Goal Seek?
Have you ever had to solve an algebra problem – one which involves some pretty difficult math – to find the value of "x"? Well, if so, Goal Seek will save you tons of time by doing the computation in seconds.
- I should note: it is helpful, for the sake of understanding, to know how to solve an algebra problem; however, this tool will allow you to skip the arduous pen and paper if you’re feeling pressed for time.
How do I use Goal Seek?
Using Goal Seek requires some setup. In any algebra problem, you have two sides of an equation: one side with an unknown or a variable, or an "x", and another side that equals a number, or an expression that equals a number. To refresh your memory, (2*x)+1 = 7 is an example of an algebra problem - we have to solve for the "x" such that (2*x) + 1 is equal to 7.
Goal Seek solves for the "x". How would you set this up in Excel? See the below screenshot!
In cell A1, we setup the equation (2*x)+1, and we link the "x" variable to cell F1, which is currently blank. In the screenshot above, since F1 is blank, the formula in cell A1 is reading it as 0, and thus the result in A1 is "1" (i.e. (2*0)+1).
Now, I know what you’re likely thinking, and I agree: This example is a simple enough algebra problem that it seems like more trouble than it's worth to plug it into Excel. But Goal Seek becomes extremely helpful with a harder algebra problem.
Let's look at the problem below.
Note: If you've taken a finance class, you might recognize the above problem as an example where you are finding the yield to maturity (the 'x') on a 5-year bond with a price of $100, par value of $100, and a coupon rate of 10%, compounded annually.
Solving for "x" becomes more of a challenge if you try and solve this example by hand. Check out the GIF below to see how to set this up using Goal Seek:
- Important note: Goal Seek only works for one-variable algebra problems. For algebra problems with multiple variables, you will likely have to use Solver, which is a different Excel tool.
And that’s it! With a little practice, this simple tool can be transformative. That’s the beauty of Excel: the more tools you have in your Excel toolbox, the quicker and more efficient you become.
Comments