The days after elections people often ask “what-if” questions. Some races are close and the vote count goes on for days. In other cases, people want to know how many votes would’ve been needed to win. Excel’s Goal Seek is good tool for answering these and other forecasting questions.
What is 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. For example, you might be looking at your local election results and see:
| Votes | % of Votes | |
| YES | 4478 | 63.90 * |
| NO | 2530 | 36.10 |
| Total | 7008 | 100 |
* Needs approval from 2/3 of the voters
In our example, the YES votes are a majority, but shy of the required 2/3 approval to win the election. People quickly realize they were close, but which item do they change to find out how close. What would’ve made a difference?
Using Goal Seek we can change the value of various cells to see how the results change. This would allow you to answer these types of questions.
- How many “NO” voters needed to be converted to YES to win the election?
- How many more votes were needed by the YES team to win the election?
- If 500 more people voted could the YES team have won?
In each of these questions, the goal is to change a data value to see if the YES percentage went over that two-thirds mark or 66.67%. Rather than haphazardly changing cell values to see the results, Goal Seek can find the answers.
How to Use Excel Goal Seek
- Create a spreadsheet in Excel that has your data.
- Click the cell you want to change. This is called the “Set cell”.
- From the Data tab, select the What if Analysis…button
- Select Goal seek.. from the drop down menu
- 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.)
- We also need to tell Excel which cell to change. Since we wanted to know the number of YES votes, we’ll click C4.
- Click OK. Excel will overwrite the previous cell value with the new one.
- If you wish to accept the new value, click OK.
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.
Goal Seek Gotchas & Requirements
I can hear some readers yelling “wait…. don’t accept the value”. Actually, people may be yelling for two reasons. If you look closely in the Goal Seek Status box, you’ll see the Target value: reads: 0.6667 came from our 66.67%. But the Current value: shows as 66.64%. Excel rounded down and the revised YES value wouldn’t win the election since 66.64% is less than 66.67%.
Another group may be asking why Excel didn’t just take 66.67% of the TOTAL vote count 7008. And that leads us to a requirement of Goal Seek – formulas. The Set cell in Step 2 must contain a formula. In our example, the initial 63.90% was a formula using the YES value (C4) divided by TOTAL value (C6). So, we met that requirement.
If I just typed the value 63.90% and tried to use Goal Seek, I would see an Excel error stating “Cell must contain a formula”.
Just as I used a formula in my set cell, I also used a formula for TOTAL in B6. This was the Sum of my YES and NO votes. Since this TOTAL cell was a formula, my TOTAL count automatically adjusted when Goal Seek changed the YES cell value. If I typed “7008” in cell C6, only the YES cell value would have changed.
The other requirement is the cell you change in Step 6 can’t contain a formula. It must be a typed value.
As this example shows, Goal Seek is a nice tool that can quickly find the answers to different situations. Although this was a simple example, you can use the same tool for complicated spreadsheets containing many variables and formulas. Just remember, you many not always like the results.