Using Operators

In Spectrum 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: argument₁ OPERATOR argument₂

Add or Edit Operator in Formula#

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.

Types of Operators#

General#

Operator	Description
( and )	The arguments of a function are contained within parentheses
;	Multiple arguments within a Spectrum-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#

Operator	Description	Result
`+`	additive operator (plus)	Adds argument₁ and argument₂
`-`	subtraction operator	Subtracts argument₂ from argument₁
`*`	multiplication operator	Multiplies argument₁ and argument₂
`\`	division operator	Divides argument₁ by argument₂
`%`	modulo or remainder operator	Returns the remainder when argument₁ is divided by argument₂

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#

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#

Operator	Description	Result
`&&`	Boolean AND	Returns a Boolean value (either true or false) if both argument₁ and argument₂ are fulfilled
`\|\|` (double pipe)	Boolean OR	Returns a Boolean value (either true or false) if either argument₁ or argument₂ 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#

See Bitwise operation and Shift.

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 2ⁿ where n is determined by you
>>	Arithemtic right shift	Divides the integer by 2ⁿ 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

Tip

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 2⁴
#sheet1!B >> 3	Returns the quotient of the value of column B from sheet1 divided by 2³