This is the fourth article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use Goal Seek in Excel.
Other articles in this series:
Watch Video – Goal Seek in Excel
Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.
For example, I have the data (as shown below), where I calculate the Monthly payment outflow with the given Loan Amount, Interest Rate and Number of Payments. [Monthly payment is in red and in round brackets as it is an outflow].
I use the following PMT formula in cell B4 to calculate the Monthly payment:
Now, I want to calculate the loan amount so that my monthly payment is $1,000. I can try and use some trial and error by putting random values in Loan amount, but there is a better way to do this – using Goal Seek in Excel.
Using Goal Seek in Excel
- Go to Data –> Data Tools –> What-If Analysis –> Goal Seek.
- In the Goal Seek Dialogue Box, use the following details:
- Set Cell: B4 (this is the cell with the goal/target).
- To Value: -1,000 (this is the goal value. Its negative here as it is an outflow).
- By Changing Cell: B1 (this is the loan amount that we want to change to achieve the goal value).
- Click OK. This will open the Goal Seek Status dialogue box.
- Goal Seek Status dialogue box will inform you when a solution is found. If you wish to accept the solution, click on the OK button, and it will change the cell values. If you do not want to accept the solution, click on Cancel.
- It may happen that the Goal Seek is not able to find a solution. It will show you the relevant prompt in that case.
- Once you accept the solution, it will be reflected in the cells.
Try it yourself..Download the file
You May Also Like the Following Excel Tutorials:
4 thoughts on “Data Analysis [Part 4 of 5] – Goal Seek in Excel”
what if you enter a negative number in the [to:value] but it doesn’t work? how do I get negatives to work?
Thank you 🙂
Bro it would really help if you explain us with few other examples coz I tried using you given formula but it ain’t working so..
Amazing! Exactly what I was looking for.
Thanks for commenting Nikhil.. Glad you liked it 🙂
Comments are closed.