Okay- my theme this week, in case you have not guessed, is What-If. Let's talk about Goal Seek. This is one feature that some people are familiar with simply because you were able actually find it in early versions of Excel under the Data menu unlike Solver and Scenario Manager that are add-in programs that you actually have to look for.
Goal seek is great to use if you are doing a simple and quick what-if. The reason for this is that Excel only allows you to change one cell with Goal Seek as opposed to 32 in Scenarios! Also, the cell you are changing cannot contain a formula. However even though it is a bit limited, it is still useful to know how to use it when you have something simple to determine. It certainly beats typing in a number, pressing Enter and looking at the answer and then typing in another number and seeing what that answer is... yes... some people still do that.. you know who you are. Try Goal Seek instead.
Goal Seek
Goal seek allows the user to find a specific value for a cell included in a calculation by changing one other variable in the equation. It is a wonderful tool if you are trying to work through a what-if scenario.
For example, suppose you want to obtain a home loan but can only afford $800 a month. With Goal Seek, you could determine the maximum home purchase price that you could afford quickly.
Goal Seek requires 3 parameters.
1. The cell you want to change. In the example above, this would be the loan amount.
2. The value to which you want to change the target cell.
3. The cell you want to change to achieve the target amount.
To Use Goal Seek
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Goal Seek from the menu.
3. In the Set Cell Box, type the cell reference of the cell that contains the formula you want to change.
4. In the To Value box, type in the value to which you want to change the cell.
5. In the By changing cell box, enter the cell reference of the cell that contains the value you want to adjust.
6. Click OK.
7. Click OK.
I have a screenshot below example below: I wanted to change the current monthly payment in H6 to $800 by changing D6 which was the value of the loan.
No comments:
Post a Comment