Logical Functions#
In Spectrum Boolean operations are completed with logical functions. Use these to conjoin, disjoin or to create if-then statements.
These are the logical functions in Spectrum:
AND#
Syntax#
AND(<boolean>; <boolean>, ...)
Description#
Checks to see if all arguments in the specified columns are true. When all arguments specified in the function are true the result is true. If any of the arguments in the columns are false the result is false. All of the arguments in the columns must be in the Boolean data type. If one of the arguments is null and precedes the first false in an argument, the calculation fails for that row. This function supports more than 255 arguments.
Instead of using the AND() function you can also use the && operator.
Examples
AND(Column1;Column2;Column3)
| Column1 | Column2 | Column3 | Result |
|---|---|---|---|
| true | true | true | true |
| true | false | false | false |
| false | true | true | false |
| false | false | false | false |
| false | false | null | false |
| true | true | null | <error> |
CASE#
Syntax#
CASE(<default value>; <any>; <case 1>; <return value 1>; <case 2>; <return value 2>; ...)
or
CASE(<default value>; <boolean condition 1>; <return value 1>; <boolean condition 2>; <return value 2>; ...)
Description#
Exchanges values for others based on either specific cases or boolean conditions. Each case is evaluated in order. If none of the specified cases apply, the function returns the default value. Parameters must appear in pairs. The return and default values must be of the same type.
Default values cannot be NULL.
Examples
CASE(0;#Column1;"org";1;"gov";2;"com";3;"mil";null)
| Column1 | CASE returns |
|---|---|
| org | 1 |
| gov | 2 |
| mil | NULL |
| biz | 0 |
| com | 3 |
| net | 0 |
CASE("10 or under";GE(#Column1;11);"Over 10")
| Column1 | CASE returns |
|---|---|
| 2 | 10 or under |
| 4 | 10 or under |
| 6 | 10 or under |
| 8 | 10 or under |
| 10 | 10 or under |
| 12 | Over 10 |
| 14 | Over 10 |
| 16 | Over 10 |
| 18 | Over 10 |
| 20 | Over 10 |
ERRORCONTAINS#
Syntax#
ERRORCONTAINS(possibleError:<any>;string:<string>)
Description#
- returns
FALSEif 'possibleError' is not an ERROR value - returns
TRUEif the ERROR message of 'possibleError' contains the 'string', returns FALSE otherwise - a NULL 'stringValue' results in a
FALSEvalue
Example
ERRORCONTAINS(#B;'failed')
| A | B | ERRORCONTAINS |
|---|---|---|
| 0 | <error> | true |
| 1 | 5.0 | false |
| 2 | 2.5 | false |
| 3 | 1.6666 | false |
| 4 | 1.25 | false |
IF#
Syntax#
IF(<boolean>;<any>;<any>)
Description#
Checks first the arguments of a column to verify a boolean data type. If the argument from the Boolean column is true the result is the argument from the column specified for true. If the argument for from the Boolean column is false the result is the argument from the column specified for false. The conditional argument must be Boolean and the result arguments must be the same data type.
You may also look at examples of how to use operators in Workbook formulas in our Using Operators section.
Examples
=IF(#Column1;#Column2;#Column3)
If column1 is true the result is column2. If column1 is false the result is column3.
| Column1 | Column2 | Column3 | Result |
|---|---|---|---|
| true | A | B | A |
| false | A | B | B |
| false | A | B | B |
| true | A | B | A |
| false | A | null | null |
| true | A | null | A |
IFERROR#
Syntax#
IFERROR(possibleError:<any>;replacement:<any>)
Description#
IFERROR returns a value that specifies if a formula evaluates to an error.
- if the expression in 'possibleError' is an ERROR value, then 'replacement' is returned, otherwise the value of 'possibleError' is returned
- both, 'possibleError' and 'replacement', must have the same type
- if 'replacement' is also an ERROR, then that error value will be the result of IFERROR
Example
IFERROR(#B;100)
| A | B | IFERROR |
|---|---|---|
| 0 | <error> | 100.0 |
| 1 | 5.0 | 5.0 |
| 2 | 2.5 | 2.5 |
| 3 | 1.666 | 1.666 |
| 4 | 1.25 | 1.25 |
ISERROR#
Syntax#
ISERROR(possibleError:<any>)
Description#
ISERROR returns TRUE if the value is any error value.
- returns TRUE if 'possibleError' is an ERROR value, otherwise FALSE.
Example
ISERROR(#B)
| A | B | ISERROR |
|---|---|---|
| 0 | <error> | true |
| 1 | 5.0 | false |
| 2 | 2.5 | false |
| 3 | 1.666 | false |
| 4 | 1.25 | false |
NOT#
Syntax#
NOT(<boolean>)
Description#
Checks the boolean arguments of a column and gives the opposite Boolean result. If the argument is true the result is false. If the argument is false the result is true. If you enter null, the result is null.
Instead of using the NOT() function you can also use the ! operator.
Examples
Checks the column1 Boolean expression and gives the opposite result.
| Column1 | Result |
|---|---|
| true | false |
| true | false |
| false | true |
| false | true |
| null | null |
OR#
Syntax#
OR(<boolean)>; <boolean>; ...)
Description#
Checks specified Boolean arguments to see if they are true or false. If any of the arguments are true the result is true. If all of the arguments are false the result is false. If null is an argument and all the other arguments are false, the result is an error. This function can support more than 255 arguments.
Instead of using the OR() function you can also use the || operator.
Examples
OR(Column1;Column2;Column3)
| Column1 | Column2 | Column3 | Result |
|---|---|---|---|
| true | true | true | true |
| true | true | false | true |
| true | false | false | true |
| false | true | true | true |
| false | false | true | true |
| false | false | false | false |
| true | true | null | true |
| true | false | null | true |
| false | true | null | true |
| false | false | null | <error> |
SELECT#
Syntax#
SELECT(<any>;<any>;<any>)
Description#
Create columns with selected values using a key constant from a specified column in the Workbook.
Select a column from a worksheet and write in a constant from that column. Next, select the value column for which to compare the constant from the original column. If the constant or original column is set as null, the cells all return empty. If the value column is set as null, the column doesn't calculate.
This is an excellent tool to create columns for use in a pivot table.
Example
Original data:
| Department | Year | Dollar |
|---|---|---|
| Department 1 | 2010 | 10 |
| Department 1 | 2010 | 20 |
| Department 1 | 2011 | 10 |
| Department 2 | 2011 | 10 |
| Department 2 | 2012 | 10 |
Create a new worksheet:
- Copy the Department column.
- Use the SELECT function:
- For column to select, use the Year column.
- Write in the specific year as the constant.
- Select the Dollar column as the value if equal.
- Use the SELECT function again, selecting a different constant.
COPY(#RawData!Department)
SELECT(#RawData!Year;2010;#RawData!Dollar)
SELECT(#RawData!Year;2011;#RawData!Dollar)
| Department | Dollar_in_2010 | Dollar_in_2011 |
|---|---|---|
| Department 1 | 10 | <empty> |
| Department 1 | 20 | <empty> |
| Department 1 | <empty> | 10 |
| Department 2 | <empty> | 10 |
| Department 2 | <empty> | <empty> |