The Goal Seek function in Excel is a way to find a solution to a problem by altering one of the underlying assumptions in order to get the desired output. This method is also known as What-if Analysis. The excel use goal seek function to solves the problem by using a form of backwards reasoning known as trial and error, which involves repeatedly feeding it guesses until it finds the correct solution.

If, for instance, the formula for revenue is equal to the number of units sold multiplied by the selling price, then then excel use goal seek function to calculate the number of units that need to be sold in order to achieve a revenue of one million dollars, provided that the selling price is known. When it comes to carrying out sensitivity analysis in financial modelling, the function is extraordinarily helpful.

How excel use goal seek function to calculate solution to get target?

Let’s suppose the commission is 10% of total sale so let’s find the solution to achieve our target commission by using the goal seek function of excel.

1. First you have to calculate the commission then check if the goal is achieved or not.

As in picture above we calculate the commission which is 1220 with the use of formula =SUM(B2:B6)*0.1 and the result is below the goal which is 2000

Now we have to find out the solution that how much sale should be so that the goal can be achieved. We have selected the cream sale should be increased so that the commission will be 2000.

Let’s find the sale of cream through the excel goal seek function.

Before we start it must be clear that excel use goal seek only with formula.

2. Select the cell in which actual commission is calculated already then Click Data then chose the What-if Analysis drop-down button and then Goal Seek.

3. Now fill all three cell of Goal Seek set cell is already filled, to value cell is your goal which 200 so put 2000 here and then, by changing is which sale you want to change to achieve your goal and that is cream so after clicking on by changing cell select the cream sale cell.

4. Then click OK to get the result.

How to set iterations to use goal seek function so that more accurate result can be achieved?

1. Click Menu go to Options and choose Calculation.

2. Iteration should be checked, and the Maximum iterations and Maximum change (accuracy) settings should be adjusted accordingly.

The calculation will end as soon as the results meet the criteria for the maximum change within the maximum number of repetitions.

This article provides a detailed and step-by-step explanation of how excel use goal seek function to compute the solution to a problem so that the desired outcome can be accomplished.

Please go to the webpage for the WPS Academy if you would like further information regarding the WPS office. You may also grab WPS Office from this page, which is a free download, and use it to edit files in Word, Excel, PowerPoint, and PDF formats.