Using Operators
- Juliane Wetzel
In Datameer X you can easily use operators in formulas, such as > (greater than), == (equal to), or + (plus). These operators can be used instead of using the functions in the Formula Builder. You can't enter an operator using the Formula Builder. You can only enter it into the Formula bar. Operators use the following syntax: argument1 OPERATOR argument2
Using Operators in Datameer
To use operators:
- Click the data area of a column. If there is already a formula associated with that column, the formula is displayed above the workbook beside the fx symbol. Your cursor is in the Formula bar and you can start typing immediately.
- Enter or edit a formula using operators.
- Press enter.
General Operators in Formulas
Operator | Description |
|---|---|
( and ) | The arguments of a function are contained within parentheses |
; | Multiple arguments within a Datameer-defined function are separated with a semicolon |
#ColumnName | References a column in the current workbook sheet |
#SheetName!ColumnName | References a column form another workbook sheet |
Arithmetic Operators
Operator | Description | Result |
|---|---|---|
+ | additive operator (plus) | Adds argument1 and argument2 |
- | subtraction operator | Subtracts argument2 from argument1 |
* | multiplication operator | Multiplies argument1 and argument2 |
/ | division operator | Divides argument1 by argument2 |
% | modulo or remainder operator | Returns the remainder when argument1 is divided by argument2 |
Examples
Formula | Returns |
|---|---|
#B + 200 | Returns the sum of the value in column B plus 200 |
| #MyList1 + #MyList2 | Returns a single list with the values of both lists concatenated ["MyList1", "MyList2"] |
#sheet1!count * 5 | Returns the product of the values in column count from sheet1 multiplied by 5 |
#B % 7 | Returns only the remainder after dividing the values in column B by 7 |
Equality and Relational Operators
These operators make it easy to determine the range of your values.
Operator | Description | Result |
|---|---|---|
> | greater than | Returns a Boolean value (either true or false) |
>= | greater than or equal to | Returns a Boolean value (either true or false) |
== | equal to | Returns a Boolean value (either true or false) |
!= | not equal to | Returns a Boolean value (either true or false) |
<= | less than or equal to | Returns a Boolean value (either true or false) |
< | less than | Returns a Boolean value (either true or false) |
You can use more than one equality or relational operator in a formula. To combine operators you can use either the AND operator && or the OR operator || (two pipes) - see below. You can also use the AND() function or the OR() function.
Examples
Formula | Returns |
|---|---|
#B < 200 | Returns true if the value in column B of the current sheet is less than 200 and false if it is greater than or equal to 200 |
#sheet1!count >= 300 | Returns true if the value in the column titled count from sheet1 is greater than or equal to 300 and false if it is less than 300 |
#B > 200 && #B < 500 | Returns true if the value of column B of the current sheet is greater than 200 and less then 500, and returns false if the value is less than 200 or greater than 500 |
AND(#B > 200; #B < 500) | This is the same as #B > 200 && #B < 500, here we are using the AND() function instead of the AND operator |
Conditional Operators
Operator | Description | Result |
|---|---|---|
&& | Boolean AND | Returns a Boolean value (either true or false) if both argument1 and argument2 are fulfilled |
|| (double pipe) | Boolean OR | Returns a Boolean value (either true or false) if either argument1 or argument2 is fulfilled |
! | Returns the opposite of a Boolean value | Returns true if the value was originally false, and returns false if the value was originally true |
Examples
Formula | Returns |
|---|---|
#B > 200 && #B < 500 | Returns true if the value of column B of the current sheet is greater than 200 and less then 500, and returns false if the value is less than 200 or greater than 500 |
#B > 500 || #B < 200 | Returns true if the value of column B of the current sheet is greater than 500 or less then 200, and returns false if the value is less than 500 but greater than 200 |
Bitwise and Shift Operators
Operator | Description | Result |
|---|---|---|
~ | Unary bitwise complement | Performs a logical negation on every bit of a number represented in binary, if the bit is originally 1 then it returns 0 |
<< | Logical and arithmetic left shift | Both a logical and an arithmetic right shift multiply the integer by 2n where n is determined by you |
>> | Arithemtic right shift | Divides the integer by 2n where n is determined by you |
>>> | Logical right shift | A bitwise operation that shifts all bits of an operand to the right |
& | Bitwise AND | Compares the binary representation of two numbers as bit pairs and returns 1 if both bits of a pair are 1, or returns 0 if both bits or one bit of the pair is 0 |
| (pipe) | Bitwise inclusive OR | Compares the binary representation of two numbers as bit pairs and returns 1 if both or one of the bits of a pair are 1, or returns 0 if both bits of a pair are 0 |
You can use a left or right shift to efficiently multiply or divide numbers using 2n. This process might be more efficient than using the operators * or / with large amounts of data.
Examples
Formula | Returns |
|---|---|
#B << 4 | Returns the product of the value in column B of the current sheet multiplied by 24 |
#sheet1!B >> 3 | Returns the quotient of the value of column B from sheet1 divided by 23 |