How to Use Goal Seek in WPS Spreadsheets to Adjust Values to a Target Total
Question details
The user needs to adjust an input value in a spreadsheet so that a calculated total reaches a specified target amount, using Goal Seek or Solver as appropriate.
- Product
- WPS Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- A quantity or input cell contributes to a total formula, and the user wants to find which quantity produces a desired total.
- Observed behavior
- The calculated total currently differs from the target amount, and the user needs to determine the required input value.
Make sure the total is a formula that depends directly or indirectly on the input cell you want to adjust, and note the current total and target value before opening Goal Seek.
Use Goal Seek to Adjust One Input Cell to a Target Total
Use this when one changing cell can make the total formula reach the desired value.
Goal Seek works backward from the formula result, changing one input cell until the formula returns the specified target.
Click the cell that contains the total formula you want to reach the target.
Go to Data > What-If Analysis > Goal Seek.
In the Set cell box confirm the total cell, enter the target amount in To value, and select the quantity or input cell in By changing cell.
Click OK. Goal Seek calculates the required input, and click OK again to keep the adjusted value.
Use Solver When Multiple Inputs or Constraints Are Involved
Use Solver if you need to adjust several quantity cells, add limits, or find the best total under constraints.
Adjust Spreadsheet Values to a Target Total with Goal Seek in WPS Office
Use WPS Spreadsheets' Goal Seek tool to change a quantity cell until a calculated total matches your target, without manually testing values. For more complex scenarios, Solver can adjust multiple cells with constraints.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the total formula and input cell.
- 2. Select the total formula cell: Click the cell that contains the total formula you want to set to the target.
- 3. Open Goal Seek: Go to Data > What-If Analysis > Goal Seek.
- 4. Enter the target and changing cell: Enter the target value in To value and select the input quantity cell in By changing cell, then click OK.
- 5. Keep the adjusted value: Review the calculated result and click OK to apply the adjusted input value to the worksheet.
Frequently Asked Questions
Where is Goal Seek in WPS Spreadsheets?
Open the Data tab, click What-If Analysis, and choose Goal Seek. The dialog box lets you set the formula cell, target value, and changing cell.
Can Goal Seek change more than one cell at once?
No. Goal Seek adjusts only one changing cell. To change multiple input cells or add constraints, use Solver from the Data tab instead.
Why does Goal Seek not find a solution?
Goal Seek may not find a solution if the target cannot be reached with the selected input, the formula does not depend on that cell, or the value is not numerically possible. Try a closer starting value or use Solver with constraints.
Do SUMIF or SUMIFS change values to reach a target total?
No. SUMIF and SUMIFS calculate conditional totals, but they do not modify input values. Use Goal Seek or Solver for automatic adjustment.
