Goal Seek

Goal Seek is a single‑variable reverse analysis tool in What‑If Analysis.

It determines the value of one input cell required for a dependent formula cell to produce a specified numeric result. Instead of evaluating how results change as inputs vary, Goal Seek starts from a target result and iteratively solves for the input value that satisfies it.

Goal Seek is useful when the desired outcome is known, but the required input is not.

Conceptual Model

Goal Seek operates on three elements:

• Changing cell – the input value to be adjusted.

• Formula cell – a cell containing a formula that depends on the changing cell.

• Desired result – the numeric value that the formula cell should evaluate to.

When a solution is found, the changing cell is updated with the computed value.

If no solution is found, the original value is restored.

Goal Seek solves for a single variable only.

Basic Programmatic Usage

Goal Seek can be invoked programmatically using the GC.Spread.Sheets.CalcEngine.goalSeek method.

// Loan amount: 10000
// Term: 18 months
sheet.setValue(0, 1, 10000);
sheet.setValue(1, 1, 18);
sheet.setFormatter(2, 1, "0%");
sheet.setFormula(3, 1, "-PMT(B3/12, B2, B1)");

// Solve for B3 so that B4 equals 600
GC.Spread.Sheets.CalcEngine.goalSeek(
  sheet, 2, 1,   // changing cell (B3)
  sheet, 3, 1,   // formula cell (B4)
  600            // desired result
);

By default:

• Maximum iterations: 200

• Tolerance: 0.001

The method returns true if a solution is found, or false otherwise (see Execution Model below for asynchronous scenarios).

Advanced Iteration Control

Goal Seek supports advanced iteration control through an optional options parameter. This enables fine‑grained control over convergence behavior, precision, and runtime observation.

Controlling Convergence

You can adjust numerical behavior using:

• maximumIterations — limits the number of iterations.

• tolerance — defines the acceptable difference between the formula result and the desired result.

Example:

GC.Spread.Sheets.CalcEngine.goalSeek(
  sheet, 2, 1,
  sheet, 3, 1,
  600,
  {
    maximumIterations: 500,
    tolerance: 1e-8
  }
);

Use tighter tolerances for high‑precision financial or engineering scenarios.

Observing Each Iteration

The callback option allows you to observe each internal iteration.

The callback receives step information including:

• Current iteration index

• Current value of the changing cell

• Current formula result

• Whether a valid solution has been found so far

Example:

GC.Spread.Sheets.CalcEngine.goalSeek(
  sheet, 2, 1,
  sheet, 3, 1,
  600,
  {
    callback: (info) => {
      console.log(
        `iter=${info.currentIteration}, ` +
        `x=${info.currentValue}, ` +
        `f(x)=${info.currentFormulaResult}, ` +
        `succeeded=${info.succeeded}`
      );
    }
  }
);

This enables:

• Logging and diagnostics

• Educational demonstrations

• Real‑time visualization of convergence

Early Termination and Asynchronous Control

If the callback returns true, Goal Seek stops immediately and keeps the current value.

The callback may also be asynchronous. When it returns a Promise, Goal Seek waits for the Promise to resolve before continuing to the next iteration.

Example:

GC.Spread.Sheets.CalcEngine.goalSeek(
  sheet, 2, 1,
  sheet, 3, 1,
  600,
  {
    callback: async (info) => {
      await new Promise(r => setTimeout(r, 100));
      if (Math.abs(info.currentFormulaResult / 600 - 1) < 1e-10) {
        return true; // stop early
      }
    }
  }
);

This allows:

• Animated convergence

• Interactive stopping logic

• Dynamic tolerance strategies

Execution Model

Return Value

• boolean — returned for synchronous execution.

• Promise — returned when:

  • Incremental calculation is enabled, or

  • An asynchronous callback is provided.

The Promise resolves to true if a solution is found, otherwise false.

Paint Behavior

• For synchronous execution, SpreadJS internally wraps Goal Seek with suspendPaint / resumePaint for performance.

• For Promise‑based execution, painting is not automatically suspended. You may call suspendPaint manually if needed.

Calculation Mode Handling

If the workbook calculation mode is not auto, Goal Seek temporarily switches it to auto during execution and restores the original mode afterward.

Behavioral Constraints

• The desired result must be a finite numeric value.

• Formulas involved in Goal Seek must evaluate synchronously.

• Asynchronous formulas are not supported.

• Goal Seek supports spilled cells in dynamic arrays.

• In Promise‑based scenarios, SJS.TABLE is supported.

• Runtime API calls do not support undo.

  The Designer UI supports undo for Goal Seek operations.

• In collaboration scenarios:

  • Runtime API sends trial updates during iteration.

  • Designer sends only the final result after confirmation.

If no solution is found, the original value of the changing cell is restored.

Algorithm Notes

SpreadJS uses its own numerical strategy:

• Secant Method is attempted first.

• If necessary, the algorithm falls back to the Bisection Method.

Due to differences in implementation, results may differ from Microsoft Excel.

In some cases where Excel fails to locate a solution due to search strategy limitations, SpreadJS may still successfully converge.