# Goal Seek

## Content

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`](/spreadjs/api/modules/GC.Spread.Sheets.CalcEngine#goalseek) method.

```javascript
// 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:

```javascript
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:

```javascript
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:

```javascript
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.