Formula support

Overview

The Formulas plugin allows Handsontable to process formula expressions defined in the provided data. This plugin uses a formula-parser library which takes most of functions from formula.js.

Features:

  • Any numbers, negative and positive as float or integer;
  • Arithmetic operations such as: +, -, /, *, %, ^;
  • Logical operations such as: AND(), OR(), NOT(), XOR();
  • Comparison operations such as: =, >, >=, <, <=, <>;
  • All JavaScript Math constants such as: PI(), E(), LN10(), LN2(), LOG10E(), LOG2E(), SQRT1_2(), SQRT2();
  • Error handling: #DIV/0!, #ERROR!, #VALUE!, #REF!, #NAME?, #N/A, #NUM!;
  • String operations such as: & (concatenation eq. =-(2&5) will return -25);
  • All excel formulas defined in formula.js;
  • Relative and absolute cell references such as: A1, $A1, A$1, $A$1;
  • Build-in variables such as: TRUE, FALSE, NULL;
  • Custom variables;
  • Nested functions;
  • Dynamic updates.

Known limitations:

  • Not working with filtering and column sorting;
  • Not working with trimming rows.

Quick setup

To enable the plugin you need to set the formulas property to true. Cells that are dependent on the edited value will be dynamically recalculated.

Custom variables

You can pass your custom variables which can be ready to use in your formula expressions. To set custom variables pass an object with key:value pairs to the formulas property.

Updating custom variables

At some point, you may want to update the custom variable's value. To do so, you need to use both setVariable and one of recalculate* methods.

Advanced example

The advanced example shows how to manage nested formulas. Also you can see how to set dependencies between different types of formulas.

Edit this page

Tutorial: Formula support