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 6^th 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