Types of operators

The operators specify what type of actions are performed on arguments (operands) in the formula. HyperFormula supports the operators that are common in spreadsheet software. They are calculated in a specific order which can be altered by the use of parentheses.

HyperFormula supports the following operators:

• Unary operators
• Binary arithmetic operators
• Comparison operators
• Concatenation operator
• Reference operators

Unary operators

The unary operators have only one argument (operand). For example, when the unary negation operation is provided with a number, it returns the negative value of that number.

Operator	Meaning	Example	Description
-	Unary minus	-a	Returns the negative of its argument.
+	Unary plus	+a	Returns the positive of its argument.
%	Percent	a%	Calculate the percent of an argument.

Binary arithmetic operators

The binary arithmetic operators enable the computation of basic mathematical operations. They don’t have to be wrapped with any functions. This table shows the basic behavior of the binary arithmetic operators:

Operator	Meaning	Example	Description
+	Addition	a + b	Add the two arguments.
-	Subtraction	a - b	Subtract the second argument from the first argument.
*	Multiplication	a * b	Multiply the two arguments.
/	Division	a / b	Divide the first argument by the second argument.
^	Exponentiation	a ^ b	Raise the first argument by the power of the second argument.

You are probably wondering why the modulo operator is missing. It is supported by the function MOD so instead of writing a % b, as you would in a regular mathematical equation, you use a formula like this: =MOD(a, b).

Comparison operators

The binary relational operators, when used in a formula, return boolean or logical values. Here are some very general rules:

Operator	Meaning	Example	Description
=	Equal to	a = b	True if a is equal to b.
<	Less than	a < b	True if a is less than b.
>	Greater than	a > b	True if a is greater than b.
<=	Less than or equal	a <= b	True if a is less than or equal to b.
>=	Greater than or equal	a >= b	True if a is greater than or equal to b.
<>	Not equal to	a <> b	True if a is not equal to b.

Type coercion

HyperFormula does type coercion and it can have an impact on comparing, adding, or any other operation between values of a different type. The tables represent some operations between different types and their results.

Boolean to int coercion, basic arithmetic operations

a) true and null

Operation	Result
true + null	1
true - null	1
true * null	0
true / null	#DIV/0!
true^null	1
+true (unary plus true)	true
-true (unary minus true)	-1
true%	0.01

b) null and true

Operation	Result
null + true	1
null - true	-1
null * true	0
null / true	0
null ^ true	0
+null (unary plus null)	null
-null (unary minus null)	0
null%	0

c) true and true

Operation	Result
true + true	2
true - true	0
true * true	1
true / true	1
true ^ true	1

d) false and true

Operation	Result
false + true	1
false - true	-1
false * true	0
false / true	0
false ^ true	0

e) true and false

Operation	Result
true + false	1
true - false	1
true * false	0
true / false	#DIV/0!
true ^ false	1

f) false and false

Operation	Result
false + false	0
false - false	0
false * false	0
false / false	#DIV/0!
false ^ false	1
+false (unary plus false)	false
-false (unary minus false)	0
false%	0

g) null and false

Operation	Result
null + false	0
null - false	0
null * false	0
null / false	#DIV/0!
null ^ false	1

Order operations, comparisons

a) Empty string ("") and null

Operation	Result
"" > null	false
"" < null	false
"" >= null	true
"" <= null	true

b) String (“string”) and boolean

Operation	Result
”string” > false	false
”string” < false	true
”string” >= false	false
”string” <= false	true

c) Null and false

Operation	Result
null > false	false
null < false	false
null >= false	true
null <= false	true

d) Null and positive integer

Operation	Result
null > 1	false
null < 1	true
null >= 1	false
null <= 1	true

e) Negative integer and null

Operation	Result
-1 > null	false
-1 < null	true
-1 >= null	false
-1 <= null	true

f) 0 and null

Operation	Result
0 > null	false
0 < null	false
0 >= null	true
0 <= null	true

g) 0 and false

Operation	Result
0 > false	false
0 < false	true
0 >= false	false
0 <= false	true
0 = false	false

h) Positive integer and true

Operation	Result
1 > true	false
1 < true	true
1 >= true	false
1 <= true	true
1 = true	false

Comparing strings

By default, HyperFormula is case and accent insensitive. This means it will ignore upper and lower-case letters and accents during the comparison. For example, if you compare AsTrOnAuT with aStroNaut they will be understood as identical, the same goes for Préservation and Preservation. It applies to comparison operators only. It can be configured with accentSensitive and caseSensitive options in the configuration.

Apart from accents and case sensitivity, you can also configure caseFirst. This option defines whether upper case or lower case should come first. Additionally the ignorePunctuation option specifies whether punctuation should be ignored in string comparison. By default caseFirst is set to 'lower' and ignorePunctuation is set to false. For more details see the official API reference of HyperFormula.

Here is an example configuration that overwrites default settings:

// this part of the configuration shows options
// related to strings only
const options = {
    caseSensitive: true,
    accentSensitive: true,
    caseFirst: 'upper',
    ignorePunctuation: true
};

Concatenation operator

The concatenation operator is used to combine multiple text strings into a single value.

Operator	Meaning	Example	Description
&	Concatenation	”a” & “b”	Concatenates two arguments
(left and right) into one

Reference operators

The reference operators are used to perform calculations of combined ranges.

Operator	Meaning	Example	Description
: (colon)	Range operator	A1:B1	Makes one reference to multiple cells between the two specified references.
, (comma)	Union operator	A1:B1,A2:B2	Returns the intersection of multiple ranges.
(space)	Intersection operator	A1:B1 A2:B2	Finds the intersection of the two ranges.