# Formula Pro v7

JavaScript spreadsheet formula parser and execution engine for Jspreadsheet.

## Compatibility

- **Formula Pro v7**: Requires Jspreadsheet Pro v13 or later
- **Formula Pro v6**: Use with Jspreadsheet Pro v12
- **Formula Pro v5**: Use with Jspreadsheet Pro v11

## Overview

Formula Pro implements a custom shunting yard parser that translates Excel-compatible formula strings into secure JavaScript operations. Every function is validated against desktop Excel, and the engine reproduces Excel argument counting, type coercion, error precedence and dynamic array spilling.

## Features

- 525 functions, covering 506 of the Excel function set
- Dynamic arrays and spilling, including the `A1#` spill range operator
- LAMBDA, LET and function values: `=BYROW(A1:B2,SUM)`, `=LAMBDA(X,LAMBDA(Y,X+Y))(1)(2)`
- Union `(A1:B2,D6)`, intersection `A1:B5 B3:C6` and 3D `Sheet1:Sheet3!A1` references
- Trim references `A1.:.E10` and TRIMRANGE
- Cross-worksheet calculations
- Range operations (A1:B10, A:A, 1:1)
- Defined names, tables and external variables
- Matrix operations
- @ operator and column name references
- Custom functions with access to the calling cell and worksheet
- Async functions (WEBSERVICE) resolved through the Jspreadsheet cell cache
- Floating-point precision adjustment, per result or per operation
- Formula caching for performance
- International customization: argument and array separators, locale-aware dates
- TypeScript definitions and a machine-readable function catalog (`formulas.json`)

## Installation

```bash
npm install @jspreadsheet/formula-pro
```

```javascript
import jspreadsheet from 'jspreadsheet';
import formula from '@jspreadsheet/formula-pro';

formula.license('###');

jspreadsheet.setExtensions({ formula });
```

## Configuration

```javascript
// Argument separator and array column separator
formula.divisor = ';';
formula.horizontalArraySeparator = '\';

// Round floating-point noise on the final result, or on every operation
formula.adjustPrecision = true;
formula.adjustPrecisionGranular = true;

// Log evaluation errors to the console
formula.debug = true;

// Transform a formula before it is evaluated
formula.onbeforeformula = (expression) => expression.toUpperCase();

// Intercept evaluation errors
formula.onerror = (error, expression, variables, x, y, worksheet) => {
    console.log(expression, error.message);
};
```

## Custom Functions

```javascript
formula.setFormula({
    'MY.DOUBLE': function (value) {
        // this.x, this.y and this.worksheet identify the calling cell
        return value * 2;
    },
});

// Remove a custom function
formula.setFormula({ 'MY.DOUBLE': null });
```

Arguments arrive already resolved, so references become values. Returning an array spills into the neighbouring cells.

## Errors

Results are compared by identity against the instances in `formula.errors`:

| Key | Value |
| --- | --- |
| `nil` | `#NULL!` |
| `div0` | `#DIV/0!` |
| `value` | `#VALUE!` |
| `ref` | `#REF!` |
| `name` | `#NAME?` |
| `num` | `#NUM!` |
| `na` | `#N/A` |
| `error` | `#ERROR!` |
| `data` | `#GETTING_DATA` |
| `calc` | `#CALC!` |
| `spill` | `#SPILL!` |
| `blocked` | `#BLOCKED!` |

Circular references return `formula.loopError` (`#LOOP!`).

## Supported Functions

| Category | Functions |
| --- | ---: |
| Statistical | 128 |
| Math and trigonometry | 89 |
| Financial | 55 |
| Engineering | 54 |
| Text | 50 |
| Lookup and reference | 39 |
| Date and time | 25 |
| Logical | 25 |
| Compatibility | 24 |
| Information | 21 |
| Database | 12 |
| Web | 3 |

The complete list with syntax, parameters and examples ships in `formulas.json`.

Not implemented: the CUBE functions, RTD, GETPIVOTDATA, STOCKHISTORY, EUROCONVERT, CALL and REGISTER.ID. These depend on external data connections and are listed in the catalog without the `pro` flag.

Full documentation: https://jspreadsheet.com/docs/formulas/functions

## License

Commercial license required. Visit https://jspreadsheet.com for licensing.

## Links

- Product page: https://jspreadsheet.com/products/formulas
- Documentation: https://jspreadsheet.com/docs
- Issues: https://github.com/jspreadsheet/pro/issues
