Search

# Data Analysis [Part 4 of 5] â€“ Goal Seek in Excel

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:

`=PMT(B2/12,B3,B1)`

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.

You May Also Like the Following Excel Tutorials:

FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

### 4 thoughts on “Data Analysis [Part 4 of 5] â€“ Goal Seek in Excel”

1. 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 ðŸ™‚

2. 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..

3. Amazing! Exactly what I was looking for.

• Thanks for commenting Nikhil.. Glad you liked it ðŸ™‚