How to Use Goal Seek with Addition or SUM in Excel
Question details
The user wants to find out how to use the Goal Seek feature to reach a specific target value using simple addition or a SUM formula.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating a required input value for a specific cell to make an addition formula reach a desired total sum.
- Observed behavior
- The user needs to configure the Goal Seek tool with the correct formula cell, target value, and variable cell to calculate the missing addend.
Ensure your target cell contains a valid formula (like simple addition or a SUM function) that directly or indirectly references the input cell you want Goal Seek to change.
Use Goal Seek with a Direct Addition or SUM Formula
Configure the What-If Analysis tool to adjust a specific input cell until your formula reaches the desired total.
This is the standard method for using Goal Seek. It works perfectly with any formula that adds values together, as long as the cell you want Excel to change does not contain a formula itself.
In your result cell, enter your addition formula (e.g., =A1+A2) or SUM formula (e.g., =SUM(A1:A5)). Make sure one of the referenced cells is empty or contains a variable value you want to calculate.
Navigate to the Data tab on the top ribbon, click on 'What-If Analysis', and select 'Goal Seek' from the dropdown menu.
In the Goal Seek window, set the 'Set cell' to your formula cell. Enter your desired total sum in the 'To value' box (for example, 100).
Click the 'By changing cell' box and select the variable cell (e.g., A2) that Excel should adjust to reach the target, then click OK.
Use Goal Seek with a Gap Formula
If you prefer to target a zero variance, use a gap formula to subtract the sum from your target value.
Use Goal Seek in WPS Spreadsheet
WPS Spreadsheet provides a built-in What-If Analysis tool, including Goal Seek. It allows you to solve for unknown variables in your SUM formulas just like Microsoft Excel, completely free of charge.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your addition data and formulas.
- 2. Access Goal Seek: Navigate to the Data tab, click on 'What-If Analysis', and choose 'Goal Seek' from the menu.
- 3. Set your parameters: Input your formula cell, desired target value, and the cell to change, then click OK to let WPS calculate the result instantly.
Frequently Asked Questions
Why is Goal Seek giving me an error when I try to change a cell?
Goal Seek requires the 'By changing cell' to be a simple hardcoded value or an empty cell. If the cell you are trying to change contains a formula, Goal Seek will not work. Ensure the changing cell is a plain value.
Can Goal Seek change multiple cells at once in a SUM formula?
No, the basic Goal Seek tool can only adjust one input cell at a time. If you need to change multiple variables simultaneously to reach a target sum, you should use the Solver add-in feature.
Why isn't Goal Seek finding an exact match for my target value?
Sometimes Goal Seek stops when it finds a value that is very close to the target due to iteration limits. You can improve its accuracy by adjusting the 'Maximum change' setting in your spreadsheet's calculation options (Options > Formulas).
Can I use Goal Seek across different worksheets?
Yes, your 'Set cell' and 'By changing cell' can be located on different worksheets within the same workbook. Just ensure that the formula in the 'Set cell' correctly references the changing cell on the other sheet.
