# SJS.TABLE Function

## Content

The `SJS.TABLE` function performs What‑If Analysis by evaluating a result cell or range against one or more sets of input values.
It returns the calculated results as a dynamic array.
The function supports one-variable and multi-variable simulations within a single formula.

## Syntax

```auto
=SJS.TABLE(result_reference, inputs1, input_cell1, ...)
```

## Parameters

| Parameter | Description |
| --------- | ----------- |
| `result_reference` | The cell or range to evaluate for each simulation. |
| `inputs` | The values to substitute into `input_cell`. Can be a cell reference, range, inline array, or a single value. |
| `input_cell` | The single cell reference that receives each input value during evaluation. |

Additional `inputs` / `input_cell` pairs can be supplied to perform multi-variable analysis. All pairs after the first are optional.

## Behavior

`SJS.TABLE` evaluates `result_reference` and returns the collected results as a spilled dynamic array.

* The result expands automatically based on the structure of the inputs.
* The original values in referenced input cells are not permanently modified.
* The formula behaves like any standard dynamic array formula.

Single-value inputs are applied to every simulation.

## Output

`SJS.TABLE` returns a dynamic array whose size depends on the shape of `result_reference` and the supplied inputs.

* A single-cell `result_reference` produces a result grid based on the input dimensions.
* A range `result_reference` preserves its shape for each simulation.

When multiple input ranges are provided, input values are evaluated according to the dimension and mapping rules described in Input and Result Rules.

>type=info
> For detailed rules about how output size is determined and how input values are matched to each simulation, see **[Input and Result Rules](/spreadjs/docs/features/what-if-analysis/data-table/sjs-table-function/input-and-result-rules)**.

## Usage Patterns

The following patterns demonstrate common ways to use `SJS.TABLE`.

### One Variable — Column Inputs

Use this pattern when input values are listed vertically and you want results to spill downward.

```auto
=SJS.TABLE(B2, A3:A10, A2)
```

Each row returns the value of `B2` evaluated with `A2` set to the corresponding value in `A3:A10`.

### One Variable — Row Inputs

Use this pattern when input values are listed horizontally and you want results to spill across columns.

```auto
=SJS.TABLE(B2, B3:H3, A2)
```

Each column returns the evaluated result for the corresponding input value.

### Two Variables

Use this pattern when the result depends on two independent input variables.

```auto
=SJS.TABLE(A2, B2:H2, B1, A3:A5, A1)
```

The returned two-dimensional array represents all combinations of the supplied row and column inputs.

### Multiple Input Variables (Beyond Two)

`SJS.TABLE` supports more than two input variable pairs within a single formula.

```auto
A1: =B1 * C1 * D1

=SJS.TABLE(A1, {1,2,3}, B1, {1,2,3}, C1, {1;-1}, D1)
```

The result includes every combination of the supplied input values.

### Using Inline Arrays or Dynamic Arrays

Inputs do not need to come from worksheet ranges. Inline arrays and dynamic array functions are supported.

```auto
=SJS.TABLE(B1, {1,2,3}, A1)

=SJS.TABLE(B1, SEQUENCE(2,2), A1)
```

The returned result matches the structure of the provided input array.

### Nesting Inside Other Functions

Because `SJS.TABLE` is a standard formula, it can be wrapped inside other functions.

```auto
=SUM(SJS.TABLE(A1, B2:B10, A2))
```

## Complete Example — Two-Variable Data Table

The following example demonstrates a complete two‑variable data table scenario.
In this example:

* Price and Discount are input variables.
* Net Income is calculated based on estimated sales volume.
* `SJS.TABLE` evaluates Net Income across multiple combinations of Price and Discount.

```javascript
var spread = new GC.Spread.Sheets.Workbook('ss');
spread.options.allowDynamicArray = true;
spread.suspendPaint();
var sheet = spread.getActiveSheet();

// Define the model inputs and formulas that will be recalculated by the data table.
sheet.setArray(0, 0, [
    ['Price', 300],
    ['Discount', 0],
    ['Sales Volume'],
    ['Net Income']
]);
sheet.setFormula(2, 1, '=ROUND(P3+O3*B1+B2*N3,0)');
sheet.setFormula(3, 1, '=B3*(B1*(1-B2)-130)');

// Use historical price and discount data to estimate future sales volume with LINEST.
sheet.getRange('N2').value('Calculate LINEST by History data');
sheet.getRange('N3').formula('=LINEST(P8:.P17,N8:.O17)');
sheet.setArray(5, 13, [
    ['History data', null, null, 'Estimated Sales Volume\nBy LINEST Result'],
    ['Price', 'Discount', 'Sales Volume'],
    [300, 0, 38],
    [250, 0.1, 62],
    [300, 0.2, 60],
    [320, 0.25, 60],
    [200, 0.1, 70]
]);

// Show the fitted sales-volume result for each historical record.
sheet.getRange('Q8:Q13').formula('=ROUND(N$3*O8+O$3*N8+P$3,0)', true);

// Build the what-if analysis grid: discounts across the top, prices down the left.
sheet.setArray(5, 0, [
    ['Predict Table', null, 'Discount'],
    [null, null, 0, 0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35]
]);

// The top-left formula cell contains the result that SJS.TABLE will reevaluate.
sheet.setFormula(6, 1, '=B4');
sheet.setValue(7, 0, 'Price');

for (let i = 0; i < 34; i++) {
    sheet.setValue(7 + i, 1, 150 + i * 10);
}

// Generate a two-variable data table.
sheet.setFormula(7, 2, '=SJS.TABLE(B7,C7:J7,B2,B8:B41,B1)');

spread.resumePaint();
```

![image](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260416.9d14b6.png?width=800)
**Result Structure**
The formula entered in C8 spills into a dynamic array of 34 rows by 8 columns.
This size is determined by:

* `C7:J7` → 8 discount values (1 × 8)
* `B8:B41` → 34 price values (34 × 1)
* `B7` → single‑cell result reference

Because `result_reference` is a single cell, the output expands to match the maximum row and column dimensions across all inputs.
The resulting grid:

* Each row corresponds to one price value.
* Each column corresponds to one discount value.
* Each cell represents the recalculated Net Income for that specific combination.

## Differences from Excel Data Tables

Compared to Excel’s legacy data tables, `SJS.TABLE`:

* Is a standard worksheet formula that can be edited and copied.
* Allows inserting or deleting rows and columns within the result range.
* Supports more than two input variables.
* Accepts array values, named ranges, and cross-sheet references.
* Can be nested inside other formulas.

## Notes

* `SJS.TABLE` does not support use with asynchronous functions. When combined with async functions that rely on `defaultValue` or `setAsyncResult`, results may be unpredictable.
* During the evaluation of `SJS.TABLE`, internal cell updates are performed without triggering collaboration synchronization. Collaboration resumes automatically after calculation completes.
* `waitForAllCalculations()` waits until all `SJS.TABLE` simulations are completed.