Skip to main content

Business Calculus with Excel

Section A.4 From Section 1.6 Using Goal Seek

Goal Seek is a tool for What If Analysis. Given a formula \(f(x)=y\text{,}\) it asks what value of \(x\) will produce a desired value of \(y\text{.}\) It should be noted that, by defualt, it thinks values are equal if within 0.001 of each other.
  • To find Goal Seek, select the "Data" tab, then select the "What-If Analysis" menu and the "Goal Seek" item. You need to identify an input cell with a number in to be the variable to change. You also need to identify an output cell to that contains a formula that depend on the input cell.
    In terms of screenshots we have a starting point 1.6.2 where the input is A3 and the output is B3 and the mune is visible in the menu bar. After choosing Goal Seek we are presented with a Goal Seek menu 1.6.3 where we identify the input and output cells and the desired vaule of the output. Excel responds with the desired results 1.6.4. There is also a screencast of the Goal Seek example 1.6.1.
  • We often want to use Goal Seek to find the intersection of two curves 1.6.5.We simply define the difference of the fuunctions as a new formula and ust it for the output.
  • Avoiding traps of Goal Seek Since Goal Seek finds results numerically, using a variant of Newton’s method, there are several cautions to keep in mind:
    • Goal Seek thinks two values are equal when they are within 0.001 of each other. It will produce an answer to \(\frac{1}{x^4}=0\text{.}\)
    • Goal seek works best when given a starting point close to an actual solution. On a function with several solutions, different starting points lead to different solutions.
    • Goal seek is confused by corners and discontinuities.
You have attempted of activities on this page.