Goal Seek in Excel

December 8, 2021
312 Views 0

Graphic skills

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.

Goal seek in Excel1.png 

 

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. Thats 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.

Goal seek in Excel2.png 

3. Then click OK to get the result.

Goal seek in Excel3.gif  

Notes:

For some complex equation, some iterative computations are needed and its 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.

Goal seek in Excel4.gif 

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

Was this helpful?