Excel: Goal Seek Command

Microsoft Excel gives you a feature to calculate the values according to different formulas based on various input parameters; e.g., if you know the amount of the credit, the credit period and the interest rate you are able to calculate the monthly assessments on the credit. But sometimes you need to do the inverse calculations e.g., to find the credit amount by the interest rate, credit period and monthly assessments. Sometimes it is possible to find the parameter value mathematically but it is no easy task when you deal with complex formulas containing a number of parameters. In order to do it Excel has an efficient function called Goal Seek. It enables you to find a parameter value with the trial-and-error method. You can spend minutes to calculate the formula value but Excel will do it for you in no time!

Let’s have a look at how the Goal Seek function works. A company sells four different products at $15, $25, $74 and $128 and can sell 1000, 500, 100 and 10 product units a month accordingly. The company’s total income is $33′680. Now we are to find how many product units priced $128 must be sold in order to get the income of $40′000. For this let’s use the Goal Seek function.

Select the ‘Goal Seek…’ item on the Tools menu.

Specify the following parameters in the opened window:

• Set Cell – the cell with a value we want to find (cell D5). There must be a formula in this cell
• To Value – the total income (40000 in our case)
• By Changing Cell – this cell contains the value that can be changed during the calculation process (trial-and-error method) (cell C4 – the number of product units to be sold). The cell format is numerical and can’t contain any formula.

Then click Ok and Excel will find the value of cell C4 automatically, it is equal to 59,375. But as the number of product units is integer the most possible value will be 59 units.

Now you do not have to use the Goal Seek feature several times in order to determine the value of a cell for several values of its parameter. Just use the Smart Goal Seek instead.