How to use Goal Seek function in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/tips/goal-seek-function-in-microsoft-excel.html
SHARE




This article is about Goal Seek function in which you will learn about the Goal Seek function and their examples.

In Excel, we have so many analytical tools, formulas, options, and Goal seek is one of them. Goal seek option allows user to change the value in one cell until the value in a second cell reaches the desire number.

Goal is a part of What-If analysis. You will find the Goal seek option under the Data tab > in the Data tools section >under the What-If analysis.

 

image 1

 

Goal Seek have 3 options: – 1st Set Cell, 2nd To Value & 3rdand By changing Cell:-

 

image 2

 

Set Cell:- Here, we enter the cell address of the cell whose value we want to fix and the cell must contain formula; otherwise it will not be linked to cell. We will change it to obtain zero profit.

To Value: - Here, we enter the suitable value which we want to see in “Set Cell”.

By changing cell: -This cell should not contain formulas or function, and here we enter the cell which we want; Goal seek changes to obtain the zero profit.

Let’s take examples to understand the use of Goal seek:-

1st Example:-

We have product’s data, and we have details of product revenue, contribution %age and Contribution amount. Now we want to return how much revenue is required to achieve the target.

 

 

image 7

 

Follow below steps:-

  • Go to Data Tab > Data tools > What-If analysis > Goal Seek.
  • First, we enter the value which we want to set to change till the desired target.
  • Select the cell $F$8.

 

image 8

 

  • In To value, we enter our target value.

 

image 9

 

  • And then we enter the cell address on the basis of which our whole calculation depends. We enter $D$8.

 

image 10

 

  • Click on ok.

 

image 11

 

2ndExample:-

We have profit summary of cosmetic products, and in this summary, we want to increase total profit amount till 15000 by changing the lower price, highest price, % sold for the highest price.

 

image 8

 

Follow below steps:-

  • Go to Data Tab > Data tools > What-If analysis > Goal Seek.
  • First we enter the value which we want to set to change till the desired target.
  • Select the cell $D$12.
  • Enter the value profit value 15000 in “To Value”box.
  • Enter $D$4 in “By changing cell”.

 

image 9

 

  • Click on Ok.

 

image 10

 

3rdExample:-

We have detail of loan amount, interest rate, No. of year and EMI value. We want to return the interest rate for EMI value 17000.

 

image 11

 

Follow below steps:-

  • Go to Data Tab > Data tools > What-If analysis > Goal Seek.
  • First we enter the value which we want to set for change till the desired target.
  • Select the cell $D$9.
  • Enter the EMI value 17000 in “To Value” box.
  • Enter $D$4 in “By changing cell”.

 

image 12

 

  • Click on ok.

 

image 13

 

In this way, we can use Goal seek option in Microsoft Excel for analysis.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

Please follow and like us:
0


Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>