# 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