Goal Seek in Excel

Goal Seek feature is used to solve an equation. Assuming a certain result of a formula, work out what one of its variables should be.

To make it more comprehensible, we take a simple case as an example.

Here is a sales table.

Commission equals to 5% of total sales (A9=SUM(B2:B6)*0.05).

Tips: Goal Seek only works when there is a formula.

Based on the sales of Lipstick, Liquid Foundation, Eye Cream and Body Lotion, the Commission now is 671.65.

At this point, we want to know that when the commission is 900, how much the sales volume of face cream should be. That’s where Goal Seek can help.

1. Click Data > the What-if Analysis drop-down button > Goal Seek.

2. Select the cell A9 in Set cell area, enter “900” in To value area, and select the cell B6 in By changing cell area.

3. Then click OK to get the result.

Notes:

For some complex equation, some iterative computations are needed and it’s impossible to calculate the exact value, but Goal Seek can work out a value that satisfies a certain margin of error. We need to set iterations for it.

1. Click Menu > Options > Calculation.

2. Check Iteration, and set the Maximum iterations and Maximum change (accuracy) as needed. When the results satisfy the maximum change within the maximum iterations, the calculation will stop.

WPS office software could be compatible with Microsoft Office, and these training contents help students or home workers finish their work efficiently.