What-IF Analysis – Chapter II – Goal Seek

June 2, 2019
Analysis

Goal Seek

Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another. You might look at these as “cause and effect” scenarios.

Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way. It lets you start with the desired result, and it calculates the input value that will give you that result.

How to Use Excel Goal Seek

  1. Create a spreadsheet in Excel that has your data.

  1. Click the cell you want to change. This is called the “Set cell”. In my example, this will be D4.
  2. From the Data tab, select the What if Analysis…button
  3. Select Goal seek. from the drop down menu
  4. In the Goal Seek dialog, enter the new “what if” amount in the To value text box. (Remember to add the percentage sign if you have one.)

In this example, we’re asking Excel to replace the contents of cell D4 which is 63.90% with 66.67%. This is the percentage needed to win the election.

  1. We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we’ll click C4.

  1. Click OK. Excel will overwrite the previous cell value with the new one.

  1. If you wish to accept the new value, click OK.

If Goal Seek Cannot Find a Solution

Excel will not be able to find a solution to display in your input cell all time. Sometimes a solution just will not exist. If this happens, you will be notified in the Goal Seek Status dialogue box.

However, just because Goal Seek can’t find a solution does not mean you believe that to be true. In fact, you may be certain a solution does exist.   If that is the case, you can try doing the following things:

  1. Change the value of the By Changing Cell field in the Goal Seek dialogue box. Change it to a value that is closer to the solution.
  2. You can also adjust the Maximum iterations under the Formulas tab of the Excel Options dialogue box. You can reach this dialogue box by going to the File tab, then clicking Options. Click Formulas on the left. Iterations are calculations. By increasing this number, Excel can try more possible solutions.
  3. Make sure that the formula cell depends upon the changing cell.