formulas

Evaluate spreadsheet-style formulas with JavaScript values and functions.

Requirements

• Node.js 22 or later

Installation

npm install @alexbosworth/formulas

Usage

Import the named evaluateFormula function and pass it a formula string:

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  formula: '1 + 2 * 3',
});

console.log(result); // 7

Write formulas without a leading =. For example, use SUM(1, 2) rather than =SUM(1, 2).

evaluateFormula accepts an object with these properties:

| Property | Required | Description | | --- | --- | --- | | formula | Yes | Formula string to evaluate | | constants | No | Named values available to the formula | | functions | No | Custom functions available to the formula |

It returns an object containing a numeric result:

{result: 7}

A final boolean result is converted to 1 for true or 0 for false.

Constants

Pass constants as finite numbers, booleans, strings, or flat arrays containing those values. Constant names are case-insensitive within formulas.

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  constants: {
    subtotal: 25,
    taxRate: 0.08,
  },
  formula: 'ROUND(SUBTOTAL * (1 + TAXRATE), 2)',
});

console.log(result); // 27

Array constants can be passed to aggregate functions:

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  constants: {values: [4, 4]},
  formula: 'SUM(VALUES) > 7',
});

console.log(result); // 1

AVERAGE, COUNT, MAX, MIN, and SUM accept one or more scalar or array arguments. COUNT ignores non-number values. COUNT, MAX, MIN, and SUM return zero for empty arrays. AVERAGE requires at least one value, and MEDIAN requires one non-empty array. Other built-in functions expect scalar arguments.

Custom functions

Pass custom functions by name through the functions object. Function names are case-insensitive within formulas, and each argument is evaluated before the JavaScript function is called.

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  constants: {
    earned: 17,
    possible: 20,
  },
  formula: 'ROUND(PERCENT(EARNED, POSSIBLE), 1)',
  functions: {
    percent: (value, total) => value / total * 100,
  },
});

console.log(result); // 85

Custom functions must return a finite number, boolean, or string. The final formula result must still be a finite number or boolean.

Formula syntax

Formulas support numbers, TRUE, FALSE, quoted strings, parentheses, and the following operators:

| Operation | Operators | | --- | --- | | Unary | +, - | | Multiplication and division | *, / | | Addition and subtraction | +, - | | Comparison | >, <, >=, <=, =, <> |

Not-equal comparisons use spreadsheet-style <> syntax:

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({formula: '3 <> 2'});

console.log(result); // 1

Built-in functions

| Function | Description | | --- | --- | | ABS(value) | Return the absolute value | | AND(value, ...) | Return true when every value is true | | AVERAGE(value, ...) | Return the average of scalar or array values | | COUNT(value, ...) | Count numeric scalar or array values | | EXACT(string1, string2) | Compare two strings exactly | | IF(condition, ifTrue, ifFalse) | Return the selected result | | MAX(value, ...) | Return the largest scalar or array value | | MEDIAN(values) | Return the median value from an array | | MIN(value, ...) | Return the smallest scalar or array value | | NOT(value) | Reverse a boolean value | | OR(value, ...) | Return true when any value is true | | RANDBETWEEN(low, high) | Return a random integer within inclusive bounds | | ROUND(value[, places]) | Round a value; places defaults to zero | | SUM(value, ...) | Add scalar or array values |

For example:

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  constants: {
    disqualified: false,
    passingScore: 70,
    score: 82,
  },
  formula: 'IF(AND(SCORE >= PASSINGSCORE, NOT(DISQUALIFIED)), 100, 0)',
});

console.log(result); // 100

EXACT compares string case and spacing:

const {evaluateFormula} = require('@alexbosworth/formulas');

const {result} = evaluateFormula({
  formula: 'EXACT("Formula", "formula")',
});

console.log(result); // 0

Errors

Invalid syntax, unknown names, unsupported values, invalid argument counts, and non-finite results cause evaluateFormula to throw an Error.

const {evaluateFormula} = require('@alexbosworth/formulas');

try {
  evaluateFormula({formula: '1 / 0'});
} catch (err) {
  console.error(err.message);
}

Testing

npm test

License

MIT