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.