Tutorial: Formula support

Formula support

This plugin is marked as ALPHA version and its API is not stable yet.

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.

Advanced example

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

Help us improve this page