Formula evaluation

Formula evaluation in Spreadsheet 2 with support for client-side calculation, Excel/Google Sheets compatibility, and custom functions.

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.

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.

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:

// 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 [email protected]

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):

// 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):

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:

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

Named Ranges

Define reusable range names (Excel-compatible):

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:

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 →

Custom Functions

Add your own functions to extend the calculation engine:

Defining Custom Functions

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

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

Or for Web Worker mode:

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

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

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

const { enqueueCalculation } = useSpreadSheetState(...)

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

Disable auto-recalc during bulk operations:

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

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

// Trigger single recalculation
state.calculateNow();

Use range formulas instead of copying:

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

Check that formulas start with =
Verify enqueueCalculation is wired correctly (if custom)

#REF! Errors

Problem: Valid formula shows #REF! error

Solutions:

Check sheet names match in sheets prop
Verify named ranges are registered
Enable iterative calculation for circular references:
```
iterativeCalculation: { enabled: true }
```

Slow Performance

Problem: UI freezes during formula entry

Solutions:

Switch to Web Worker mode:
```
calculationMode: 'worker'
```
Avoid volatile functions in large ranges
Use manual calculation mode for bulk imports

Custom Functions Not Working

Problem: Custom function returns #NAME! error

Solutions:

Verify function is registered in functions prop
For Web Worker mode, ensure function is in worker file
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

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

Support

For formula evaluation issues:

Last Updated: 2025-01-22

PreviousHeadless UI NextImperative Spreadsheet API

Last updated 5 days ago

Was this helpful?

Calculation Modes

1. Main Thread Calculation (Default)

2. Web Worker Calculation

Excel/Google Sheets Compatibility

Supported Features

Formula Errors

Array Formulas

Circular References

Named Ranges

Structured References (Excel Tables)

Supported Functions

Custom Functions

Defining Custom Functions

Registering Custom Functions

Function Autocomplete

Performance Optimization

Tips for Large Workbooks

Benchmarks

Troubleshooting

Formulas Not Updating

#REF! Errors

Slow Performance

Custom Functions Not Working

Package Information

Installation

Further Reading

Support