In Excel what if analysis lets you answer questions with data. With what if analysis Excel also lets you experiment with data.
To use Goal Seek (example 2):
Let's say you're planning an event and want to invite as many people as you can without exceeding a budget of $500. We can use Goal Seek to figure out how many people to invite. In our example below, cell B4 contains the formula =B1+B2*B3 to calculate the total cost of a room reservation, plus the cost per person.
- Select the cell whose value you want to change. In our example, we'll select cell B4.
Selecting cell B4
- From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu.
Selecting Goal Seek from the drop-down menu
- A dialog box will appear with three fields:
- Set cell: This is the cell that will contain the desired result. In our example, cell B4 is already selected.
- To value: This is the desired result. In our example, we'll enter 500 because we only want to spend $500.
- By changing cell: This is the cell where Goal Seek will place its answer. In our example, we'll select cell B3 because we want to know how many guests we can invite without spending more than $500.
- When you're done, click OK.
Entering the desired values into the dialog box and clicking OK
- The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
- The result will appear in the specified cell. In our example, Goal Seek calculated the answer to be approximately 18.62. In this case, our final answer needs to be a whole number, so we'll need to round the answer up or down. Because rounding up would cause us to exceed our budget, we'll round down to 18 guests.
The completed Goal Seek and the calculated value
As you can see in the example above, some situations will require the answer to be a whole number. If Goal Seek gives you a decimal, you'll need to round up or down, depending on the situation.