If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Sunday, May 21, 2017

Using What-If Analysis – Goal Seek- for finding a result by adjusting input


Using What-If Analysis – Goal Seek- for finding a result by adjusting input

In case we are aware of the result from a formula but don’t know the input value that formula needs for that result, we can use the Goal seek feature.

Please note this will work with one variable input value only.

Example to understand Goal Seek.

We want to have an Average of 15 in cell B7. The current Average of 5 values is 14.2. With our 6th value in B6, we want the Average as 15 but don’t know the input value in B6. The output or Average returning cell B7 have formula of Average and range include the blank or input cell B6.

So let’s use the Goal Seek. Go to Data tab, and under What-If Analysis click on option of Goal Seek.



 
A window will open. So in first option ‘Set Cell’ we will mention the cell where we want our output, in our case it is B7 where we want our Average as 15. And To Value is 15, so by changing Cell B6, the input value, we want Value 15 in Cell B7.

Now press ok, excel will calculate the value and return, pressing ok will populate it in sheet.

So we can see it is 19. 



Value 1

17

Value 2

15

Value 3

14

Value 4

13

Value 5

12

Value 6

19

Average

15

 

No comments:

Post a Comment