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:

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

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

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:

const { enqueueCalculation } = useSpreadSheetState(...)

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

1. Disable auto-recalc during bulk operations:

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

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

// Trigger single recalculation
state.calculateNow();

1. 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:

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:

   iterativeCalculation: { enabled: true }

Slow Performance

Problem: UI freezes during formula entry

Solutions:

1. Switch to Web Worker mode:

   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

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

Further Reading

• Custom Functions Guide

• Array Formulas Guide

• Named Ranges Setup

• Structured References

• Performance Optimization

Support

For formula evaluation issues:

• GitHub: rowsncolumns/spreadsheet/issues

• Email: [email protected]

• Documentation: docs.rowsncolumns.app

---

Last Updated: 2025-01-22