# Formula evaluation

Spreadsheet 2 provides a powerful formula evaluation engine with Excel/Google Sheets compatibility. Formula calculations can run on the main thread or in a Web Worker for better performance.

## Calculation Modes

The `useSpreadsheetState` hook supports two calculation modes:

### 1. Main Thread Calculation (Default)

Formulas are evaluated synchronously on the main UI thread. Best for smaller workbooks or when you need immediate results.

```typescript
import { useSpreadsheetState } from '@rowsncolumns/spreadsheet-state';

const MySpreadsheet = () => {
  const state = useSpreadsheetState({
    calculationMode: 'single', // Default
    initialSheets: [
      {
        id: 1,
        name: 'Sheet1',
        cells: [
          { rowIndex: 1, columnIndex: 1, formattedValue: '5' },
          { rowIndex: 1, columnIndex: 2, userEnteredValue: '=A1*2' }
        ]
      }
    ]
  });

  return (
    <CanvasGrid
      {...state}
      rowCount={1000}
      columnCount={26}
    />
  );
};
```

**Pros:**

* Simpler setup (no Web Worker needed)
* Immediate results (no async overhead)
* Easier debugging

**Cons:**

* Can block UI for complex formulas
* Not suitable for workbooks with thousands of formulas

### 2. Web Worker Calculation

Formulas are evaluated asynchronously in a Web Worker thread. Best for larger workbooks or when you need non-blocking calculation.

```typescript
const createCalculationWorker = () => {
  if (typeof Worker === "undefined") {
    throw new Error(
      "The calculation worker requires a browser environment. " +
        "Provide a custom createCalculationWorker when rendering on the server."
    );
  }
  return new Worker(new URL("./calculation-worker.ts", import.meta.url), {
    type: "module",
  });
};
const MySpreadsheet = () => {
  const state = useSpreadsheetState({
    calculationMode: 'worker',
    createCalculationWorker,
    initialSheets: [/* ... */]
  });

  return <CanvasGrid {...state} />;
};
```

**Creating the Worker File:**

```javascript
// calculation-worker.ts
import { functions } from "@rowsncolumns/functions";
import { registerCalculationWorker } from '@rowsncolumns/calculation-worker/worker';

registerCalculationWorker({
  functions
});
```

**Pros:**

* Non-blocking UI during calculations
* Handles large workbooks smoothly
* Can leverage multiple CPU cores

**Cons:**

* Requires Web Worker setup
* Async results (slight delay)
* More complex debugging

## Excel/Google Sheets Compatibility

### Supported Features

We try to support full formula compatibility. If we have missed anything, let us know by email us at <support@rowsncolumns.app>

#### Formula Errors

All standard Excel error types are supported:

| Error     | Description                | Example                             |
| --------- | -------------------------- | ----------------------------------- |
| `#REF!`   | Invalid reference          | `=A1` (in cell A1)                  |
| `#NAME!`  | Unknown function           | `=UNKNOWNFUNC()`                    |
| `#VALUE!` | Wrong value type           | `=1 + "text"`                       |
| `#DIV/0!` | Division by zero           | `=1/0`                              |
| `#N/A`    | Value not available        | `=VLOOKUP("missing", A:B, 2)`       |
| `#NUM!`   | Invalid numeric value      | `=SQRT(-1)`                         |
| `#SPILL!` | Array blocked by data      | `=SEQUENCE(10)` when cells occupied |
| `#NULL!`  | Invalid range intersection | `=A1:A10 B1:B10`                    |

#### Array Formulas

Dynamic array formulas that spill across multiple cells (Excel 365 style):

```typescript
// Entering "=SEQUENCE(5)" in A1 automatically fills A1:A5
// A1: 1
// A2: 2
// A3: 3
// A4: 4
// A5: 5

// Works with 2D arrays
// =SEQUENCE(3, 2) creates a 3-row, 2-column array
```

**Spill Collision Handling:**

* If spill range overlaps user data, formula returns `#SPILL!` error
* Editing a spill cell clears the array and allows user input
* Deleting a spill cell restores the array formula

#### Circular References

Enable iterative calculation to resolve circular references (like Excel):

```typescript
const state = useSpreadsheetState({
  iterativeCalculation: {
    enabled: true,
    maxIterations: 100,    // Excel default: 100
    maxChange: 0.001       // Excel default: 0.001
  }
});

// Example: A1 = A1 + 1
// Without iterative: Shows #REF! error
// With iterative: Converges to stable value
```

**How it works:**

1. Formulas are evaluated repeatedly until convergence
2. Stops when change between iterations < `maxChange`
3. Returns `#NUM!` if doesn't converge after `maxIterations`

#### Named Ranges

Define reusable range names (Excel-compatible):

```typescript
const state = useSpreadsheetState({
  namedRanges: [
    {
      namedRangeId: 'sales-total',
      name: 'SalesTotal',
      range: {
        sheetId: 1,
        startRowIndex: 1,
        endRowIndex: 100,
        startColumnIndex: 1,
        endColumnIndex: 1
      }
    }
  ]
});

// Use in formula: =SUM(SalesTotal)
```

**Features:**

* Case-insensitive (SalesTotal = salestotal)
* Can reference ranges or single values
* Cross-sheet support
* Auto-updates when range changes

#### Structured References (Excel Tables)

Excel-style table references with column names:

```typescript
const state = useSpreadsheetState({
  tables: [
    {
      id: 'inventory',
      title: 'Inventory',
      sheetId: 1,
      range: {
        sheetId: 1,
        startRowIndex: 1,
        endRowIndex: 10,
        startColumnIndex: 1,
        endColumnIndex: 3
      },
      headerRow: true,
      columns: [
        { name: 'Product' },
        { name: 'Quantity' },
        { name: 'Price' }
      ]
    }
  ]
});

// Formula examples:
// =Inventory[Quantity]           → Qty column (B2:B10)
// =Inventory[[#Headers],[Price]] → Price header (C1)
// =Inventory[@Quantity]          → Current row Qty
// =SUM(Inventory[Price])         → Sum of Price column
```

**Supported Specifiers:**

* `#Headers` - Header row
* `#Data` - Data rows only
* `#All` - Entire table including headers
* `#Totals` - Total row
* `@ThisRow` - Current row (implicit in `[@ColumnName]`)

### Supported Functions

The calculation engine includes 300+ Excel-compatible functions:

**Math & Trig:**

* `SUM`, `AVERAGE`, `COUNT`, `MIN`, `MAX`
* `ROUND`, `FLOOR`, `CEILING`, `ABS`, `SQRT`
* `SIN`, `COS`, `TAN`, `PI`, `POWER`

**Logical:**

* `IF`, `AND`, `OR`, `NOT`
* `IFERROR`, `IFNA`, `IFS`
* `SWITCH`, `TRUE`, `FALSE`

**Text:**

* `CONCAT`, `CONCATENATE`, `TEXTJOIN`
* `LEFT`, `RIGHT`, `MID`, `LEN`
* `UPPER`, `LOWER`, `PROPER`, `TRIM`
* `FIND`, `SEARCH`, `SUBSTITUTE`, `REPLACE`

**Lookup & Reference:**

* `VLOOKUP`, `HLOOKUP`, `XLOOKUP`
* `INDEX`, `MATCH`
* `INDIRECT`, `OFFSET`, `ROW`, `COLUMN`
* `FILTER`, `SORT`, `UNIQUE`

**Date & Time:**

* `NOW`, `TODAY`, `DATE`, `TIME`
* `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`
* `DATEDIF`, `NETWORKDAYS`, `WORKDAY`

**Array Functions:**

* `SEQUENCE`, `RANDARRAY`, `UNIQUE`
* `SORT`, `FILTER`, `TRANSPOSE`
* `MAKEARRAY`, `BYCOL`, `BYROW`

**Financial:**

* `PMT`, `PV`, `FV`, `RATE`, `NPER`
* `IPMT`, `PPMT`, `NPV`, `IRR`

**Statistical:**

* `STDEV`, `VAR`, `MEDIAN`, `MODE`
* `PERCENTILE`, `QUARTILE`
* `CORREL`, `COVARIANCE`

[Full function list →](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/functions/README.md)

## Custom Functions

Add your own functions to extend the calculation engine:

### Defining Custom Functions

```typescript
import FormulaParser from '@rowsncolumns/fast-formula-parser';

const customFunctions = {
  // Simple function
  DOUBLE: (parser: FormulaParser, value: number) => {
    return value * 2;
  },

  // Function with multiple arguments
  GREET: (parser: FormulaParser, firstName: string, lastName: string) => {
    return `Hello, ${firstName} ${lastName}!`;
  },

  // Context-aware function (accesses cell position)
  CURRENT_ROW: (parser: FormulaParser) => {
    return parser.position.row;
  },

  // Async function (for API calls, etc.)
  FETCH_PRICE: async (parser: FormulaParser, symbol: string) => {
    const response = await fetch(`/api/stock/${symbol}`);
    const data = await response.json();
    return data.price;
  },

  // Function with cancellation support
  SLOW_API: async (parser: FormulaParser, url: string) => {
    const response = await fetch(url, {
      signal: parser.position.signal // Cancels on recalc
    });
    return await response.text();
  }
};
```

### Registering Custom Functions

```typescript
const state = useSpreadsheetState({
  calculationMode: 'sync',
  functions: customFunctions
});
```

Or for Web Worker mode:

```javascript
// calculation-worker.js
import { registerCalculationWorker } from '@rowsncolumns/calculation-worker/worker';
import { myCustomFunctions } from './custom-functions';

registerCalculationWorker({
  functions: myCustomFunctions
});
```

### Function Autocomplete

Add descriptions for autocomplete suggestions:

```typescript
const functionDescriptions = [
  {
    datatype: "Number",
    title: "DOUBLE",
    syntax: "DOUBLE(value)",
    description: "Returns double the input value.",
    example: "DOUBLE(5)",
    usage: ["DOUBLE(A1)", "DOUBLE(10)"],
    parameters: [
      {
        title: "value",
        description: "The number to double."
      }
    ]
  }
];

// Pass to CanvasGrid
<CanvasGrid
  functionDescriptions={functionDescriptions}
  {...state}
/>
```

## Performance Optimization

### Tips for Large Workbooks

1. **Use Web Worker mode** for workbooks with >1000 formulas
2. **Avoid volatile functions** (`NOW()`, `RAND()`) in large ranges
3. **Batch updates** when modifying multiple cells:

```typescript
const { enqueueCalculation } = useSpreadSheetState(...)

for (let i = 1; i <= 100; i++) {
  enqueueCalculation({ position: { rowIndex: i, columnIndex: 1, sheetId: 1 }, type: 'add' });
}

```

4. **Disable auto-recalc** during bulk operations:

```typescript
const state = useSpreadsheetState({
  recalculateOnOpen: false,
});

// Import 1000 rows without recalculating
state.onChangeBatch(...);

// Trigger single recalculation
state.calculateNow();
```

5. **Use range formulas** instead of copying:

```typescript
// Bad: 1000 cells with =A1*2, =A2*2, =A3*2...
// Good: One array formula =A1:A1000*2
```

### Benchmarks

Typical performance on modern hardware:

| Operation                            | Dataset       | Time     |
| ------------------------------------ | ------------- | -------- |
| Simple formula (`=A1*2`)             | 1 cell        | <1ms     |
| Range formula (`=SUM(A1:A100)`)      | 100 cells     | 2-5ms    |
| Complex dependency chain (10 levels) | 1000 formulas | 50-100ms |
| Array formula (`=SEQUENCE(1000)`)    | 1000 cells    | 10-20ms  |
| Large workbook recalc                | 10K formulas  | 500ms-2s |

## Troubleshooting

### Formulas Not Updating

**Problem:** Changing a cell doesn't recalculate dependents

**Solutions:**

1. Check that formulas start with `=`
2. Verify `enqueueCalculation` is wired correctly (if custom)

### #REF! Errors

**Problem:** Valid formula shows #REF! error

**Solutions:**

1. Check sheet names match in `sheets` prop
2. Verify named ranges are registered
3. Enable iterative calculation for circular references:

   ```typescript
   iterativeCalculation: { enabled: true }
   ```

### Slow Performance

**Problem:** UI freezes during formula entry

**Solutions:**

1. Switch to Web Worker mode:

   ```typescript
   calculationMode: 'worker'
   ```
2. Avoid volatile functions in large ranges
3. Use manual calculation mode for bulk imports

### Custom Functions Not Working

**Problem:** Custom function returns #NAME! error

**Solutions:**

1. Verify function is registered in `functions` prop
2. For Web Worker mode, ensure function is in worker file
3. Check function name matches formula (case-insensitive)

## Package Information

The formula evaluation system is provided by:

* **Package:** `@rowsncolumns/calculation-worker`
* **Version:** 1.0.15+
* **License:** MIT

### Installation

```bash
npm install @rowsncolumns/calculation-worker
# or
yarn add @rowsncolumns/calculation-worker
```

## Further Reading

* [Custom Functions Guide](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/functions/named-functions.md)
* [Array Formulas Guide](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/functions/array-formulas.md)
* [Named Ranges Setup](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/configuration/features/named-ranges.md)
* [Structured References](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/configuration/features/structured-references/README.md)
* [Performance Optimization](https://github.com/rowsncolumns/spreadsheet/blob/main/docs/getting-started/configuration/features/calculate-on-demand.md)

## Support

For formula evaluation issues:

* GitHub: [rowsncolumns/spreadsheet/issues](https://github.com/rowsncolumns/spreadsheet/issues)
* Email: <support@rowsncolumns.app>
* Documentation: [docs.rowsncolumns.app](https://docs.rowsncolumns.app)

***

**Last Updated:** 2025-01-22


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.rowsncolumns.app/getting-started/formula-evaluation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.