If you know the result you want from a formula, but you are unsure of what input value the formula requires to get that result, you can use the Goal Seek feature in Microsoft® Excel®. Goal Seek takes a result and determines possible input values that produce the result you are looking for. Data analysis can be enhanced with the use of Excel’s What-If analysis.
In this tip we look at how you can use the Goal Seek to get a preferred result by adjusting one of your input values. Let’s say you own a hotel and would like to know the number of rooms to rent out in order to make a profit of $20,000. You can use Goal Seek to achieve the result by adjusting the rooms to rent out variable.
Note: You are welcome to download the workbook to practice
Applies To: Microsoft® Excel® 2013 and 2016
Apply these easy steps to use Excel’s Goal Seek feature:
1. Select the Goal Seek
2. Select cell B4. Note the formula used to calculate the required profit. (Rooms rented *Price per room )- Expenses.
3. Select What if Analysis under the Data tab and then Goal Seek.
4. In the Set cell box, select cell B4, the cell with the formula you want to resolve.
5. In the To value box, enter 20000.
6. Place the cursor in the By Changing box and select cell B5.
7. Select OK twice. The result will be as below.
As you can see the number of rooms to rent out were easily calculated without using a complex formula. It is important to remember that the Goal Seek option can only adjust one variable at a time. To adjust multiple variables, use the Solver option, which we will cover in next week’s tip.
