Goal Seek is one of those tools that can stop you from scratching your head and give you the answers to problems like what interest rate can you afford for your loans, or how much should you sell a product for to make a profit. Essentially, any scenario where you have a formula and a target value can be solved with Goal Seek. Excel is going to run different values against your formula until it reaches the target. This is called an ‘iterative calculation’. After a number of iterations, you will get the result.
Let’s say your grade average is 68 before the final exam, and it’s just 2 points shy of a 70 passing grade. What should you score in the final to pass the class? This is the type of question Goal Seek can find an answer to. You can download this workbook here.
You might be thinking, wait a minute, I can actually calculate this by multiplying the grade column by 7, and then add all the notes. And finally, subtract them from the weighed grade value (This is all, of course, assuming that all grades affect the final score equally). And you are right! This can be calculated by entering a chain of formulas into the final grades column. However, writing this formula might actually take a bit longer than you might think. And, dealing with more complicated examples, it can take a great deal of math expertise to come up with the answer.
Goal seeking is the operation of backward calculations in computing. Excel’s Goal Seek follows this principle and tries finding an answer to your query by trial and error. Let’s see how we can use Goal Seek to find out what our final score should be to be able to pass Algebra, as ironic it may sound.
Begin with going to the Data tab, and then click What-If Analysis. Selecting Goal Seek from the drop-down menu will bring up the dialog box to set our parameters.
We need to tell Excel three things, the reference cell that contains the formula we want to work on (Set cell), to what value should the model run (To value), and finally where the results should go (By changing cell).
- Set cell: This is the cell that will contain the desired result. In our example, this is the average grade in cell I3.
- To value box: Enter the goal or formula result that you are looking to find. The target grade is 70 in our example.
- By changing cell: Enter the reference for the cell that contains the value to be modified. In the example, we are looking to find the final grade H3.
When you are done, click OK and wait for Goal Seek to work its magic. Excel should find the solution after several iterations, and then will print the value into the selected cell when you press OK. Goal Seek shows us that we need to get at least an 82 in the final to be able to pass this class.
Goal Seek is a very useful feature that can help you solve many problems much faster. For other problems, you may not see an easier way but using Goal Seek. It’s so easy to use, you can compare different scenarios, and analyze or optimize your model within a very short amount of time. Thanks to the modern day computers, solving difficult problems by trying every possible number no longer takes ages.
