Excel includes powerful tools for performing complex mathematical calculations, including What-if Analysis.
Excel includes powerful tools for performing complex mathematical calculations, including What-if Analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this article, you will learn how to use an analysis tool called Goal Seek.
Goal Seek
Whenever you create a formula or function in Excel, you have to combine different parts to calculate the result. Goal Seek works the opposite way: It lets you start with the desired result, and the tool will calculate the input value to give you that result. This article will use a few examples to show how to use Goal Seek.
Example 1 of how to use Goal Seek
Let's say you've enrolled in a course. You currently have 65 points, and you need at least 70 points to pass. Luckily, you have one final assignment that can help raise your average grade. You can use Goal Seek to find out the score you need to achieve on that final assignment to pass the course.
In the image below, you can see that the scores for the first four assignments are 58, 70, 72, and 60. While the score for the fifth assignment is unknown, you can write a formula—or function—to calculate the final required score. In this case, each assignment has an equal weight, so all we have to do is average the scores for all five by entering =AVERAGE(B2:B6). When using Goal Seek, cell B6 will display the minimum score required for that final assignment.
1. Select the cell containing the value you want to change. Whenever using Goal Seek, you will need to select a cell that already contains a formula or function. For example, we would select cell B7 because it contains the formula =AVERAGE(B2:B6).
2. From the Data tab , click the What-If Analysis command , then select Goal Seek from the drop-down menu.
3. A dialog box will appear with three fields. The first field, Set cell: , will contain the desired result. In this example, cell B7 has been selected.
The second field, To value: , is the desired result. For example, you would enter 70 because you need to earn at least that amount to pass the class.
The third field, By changing cell: , is where Goal Seek will place its answer. For example, we would choose cell B6 because we want to specify the score to be achieved in the final exercise.
4. When you're finished, click OK.
5. A dialog box will tell you whether Goal Seek was able to find the answer. Click OK.
6. The result will appear in the designated cell. In the example, Goal Seek calculated that a score of at least 90 points would be needed on the final assignment to pass the course.
Example 2 of how to use Goal Seek
Let's say you're planning an event and want to invite as many people as possible without exceeding your $500 budget. You can use Goal Seek to find out how many people to invite. In the example below, cell B5 contains the formula =B2+B3*B4 to calculate the total booking cost plus the cost per person.
1. Select the cell containing the value you want to change. For example, we would select cell B5.
2. From the Data tab , click the What-If Analysis command , then select Goal Seek from the drop-down menu.
3. A dialog box will appear with three fields. The first field, Set cell: , will contain the desired result. In this example, cell B5 has been selected.
The second field, To value: , is the desired result. For example, you would enter 500 because you only want to spend $500.
The third field, By changing cell: , is where Goal Seek will place its answer. For example, it would select cell B4 because it wants to know how many guests it can invite without spending more than $500.
4. When you're finished, click OK.
5. A dialog box will tell you whether Goal Seek was able to find the answer. Click OK.
6. The result will appear in the designated cell. In the example, Goal Seek calculated the answer to be approximately 18.62. In this case, the final answer needs to be an integer, so the answer needs to be rounded up or down. Since rounding up would exceed the budget, the example will round down to 18 guests.
As you can see in the example above, some situations will require the answer to be an integer. If Goal Seek gives you a decimal number, you will need to round up or down, depending on the situation.
Other types of what-if analysis
For more advanced projects, you might want to consider other types of hypothetical analysis: Scenarios and data tables. Instead of starting from the desired outcome and working backward, as with Goal Seek, these options allow you to test multiple values and see how the results change.
Scenarios allow you to replace values for multiple cells (up to 32) at once. You can create as many scenarios as you like and then compare them without manually changing the values. The example below uses scenarios to compare different locations for an upcoming event.
Data tables allow you to take one or two variables in a formula and replace them with as many different values as you like, then view the results in a table. This option is particularly powerful because it displays multiple results at once, unlike scenarios or Goal Seek. In the example below, 24 possible results for a car loan can be viewed.