Goal Seek Feature in Microsoft Excel

Manish Tewatia
11y
14.5k
0
2

Article

Introduction

Excel is an electronic spreadsheet program for storing, organizing and manipulating data, it is used extensively in fields such as economics and finance because it's an extremely useful tool that can be deceptively simple to use, making it apparently perfect for ad-hoc calculations.

Great Features

We have many awesome features that will do the work for you. A few of them are Hyperlink, Clip Art, Charts, Tables, Functions, Images and Backgrounds, Macros, Database, Sorting and Filter, Data Validations, Grouping, Page Layout, and many more.

One great feature among them is Goal Seek.

Goal Seek

Goal Seek is basically a tool that enables you to find the input values needed to achieve a goal or objective, you can determine what you need to do to meet your expectations given as many parameters as you need. When you want to use Goal Seek there will be three boxes that you need to fill in.

The first "Set cell."
The second "To value."
The third "By changing cell."

Where is this feature in Excel

Go to the Data menu.
Click "What-If Analysis", under this you will see the Goal Seek option.

Let's use an example to understand Goal Seek completely.

In this example I try to calculate the number of EMI of a specific Loan on the basis of various monthly EMI Amounts.

Here I suppose I buy an iPhone on EMI and I need to pay the total amount that is 45000 in 12 EMI's. So, first insert data for that as in the following figure:

Now calculate the monthly EMI using Excel builtin formulas and we have a PMT formula in Excel to calculate the monthly loan EMI.

Click on cell B5 and insert the following formula:

=PMT(B2/12,B3,-B4)

and press Enter.

Now the value or amount that you need to pay as a monthly EMI will appear in the cell B5 that is Rs 3790.75/Month. As shown in the following figure:

Now using Goal Seek

Go to Data and choose Goal Seek under What-If Analysis and the Goal Seek window will appear on the screen.

Here you see three fields that you need to fill as in the following:

Set cell : In this field you need to enter the cell address of the cell whose value you want to fix or set to a specific number and this cell must contain a formula or function.
To value : In this field you need to enter the appropriate value that you wish to see in that.
By changing cell : Here you need to enter the value of that cell that you want Goal Seek to change to obtain a value and this cell must contain a formula or function.

First Column: In the Set cell I entered the value of cell B5

Second Column: In the To value I entered the value that I want to pay as EMI for my iPhone that is 3000.

Third Column: In the By changing cell I entered the value of B3 because it will be affected.