The days after an election people often ask “what-if” type questions. Some races are close and counting can go on for days. In other cases, people want to know how many votes would’ve been needed to win. Learn how to use Goal Seek in Excel to 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. It’s useful to answer “what if” type questions because you can adjust one cell entry to see the result. The tool is often used in finance, sales, and forecasting scenarios, but there are other uses.
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 one variable and see how the results change. This would allow you to answer these types of questions.
- How many more YES votes were needed 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 one data value to see if the YES percentage went over that two-thirds mark or 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. In the example below, the green cells have formulas to calculate the percentage and sum totals.
- Click the cell you want to change. This is called the “Set cell”. In my example, this will be D4.
- 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.
- 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 0.64 with 0.67. This is the percentage needed to win the election. Technically, we need 66.7% to win, but since Goal Seek has an approximation algorithm, I don’t want to run the risk of Excel rounding down.
Goal Seek Requirements & Gotchas
As with many simple tools, there are limitations. This is true of Goal Seek. Excel only allows you to change one variable. This may be fine for many scenarios, but it presents a problem here. If you look at the latest figures, you’ll see it also increased the total vote count. Ideally, I would want to add a constraint and not have my total vote count go over the current count.
Excel also requires that the Set cell be a formula. In my case I have =C4/C6 to get a percent.
I’ve also noticed that when I formatted column D as a percentage, I ran into 2 additional issues. The first was I had to add the % sign in the To value:. Otherwise, the tool would go off and do a large number of calculations and give a bogus answer. Moreover, I ran into the approximation issue and Goal Seek rounded down my percentage to below 67%.
Just as I used a formula in my set cell, I also used an Excel 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’t use the same tool for complicated spreadsheets containing many variables and formulas. For that, you should use Excel Solver which is an Add On.
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-01-14