# 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.