General Functions#
These general functions perform basic operations on the arguments in a Workbook column. Use these functions to copy an entire column, see if integers are even or to represent a text string as an integer, etc.
The following are the general functions in Spectrum:
COALESCE#
Syntax#
COALESCE(<any>;...)
Description#
Returns the first non-null argument value for non-string values. It returns the first non-empty argument value for strings. COALESCE is a commonly used SQL function.
Examples
| Column1 | Column2 | COALESCE(#Column1;#Column2) | COALESCE(#Column2;#Column1) |
|---|---|---|---|
| 1 | 55 | 1 | 55 |
| 2 | <null> | 2 | 2 |
| <null> | 57 | 57 | 57 |
| 4 | 58 | 4 | 58 |
COPY#
Syntax#
COPY(<any>)
Description#
Copies values from one column and inserts them into a new column.
INFO: This can also be written in the Fx box as: =#SHEET_NAME!COLUMN_NAME
Examples
| Column1 | COPY returns |
|---|---|
| 23 | 23 |
| Spectrum | Spectrum |
| Mar 2, 2002 1:24:12 PM | Mar 2, 2002 1:24:12 PM |
DENULLIFY#
Syntax#
DENULLIFY(<any>;<any>)
Description#
Converts null values (represented by ω) to non-null default values. All non-null values are left untouched. Integer null values are converted to 0 (zero), float values are converted to 0.0, string null values are converted to empty strings, Boolean values are converted to false, and null date values are converted to Jan 1, 1970 12:00:00 AM. An additional argument can be entered into DENULLIFY as a default replacement to null values.
Examples
Example 1#
=DENULLIFY(#Column1
| Data Field Type | Column1 | DENULLIFY Returns |
|---|---|---|
| Integer | 1 | 1 |
| Integer | null (ω) | 0 |
| Integer | 3 | 3 |
| Float | 1.1 | 1.1 |
| Float | null (ω) | 0.0 |
| Float | 2.2 | 2.2 |
| Date | Sep 19, 2008 11:13:27 AM | Sep 19, 2008 11:13:27 AM |
| Date | null (ω) | Jan, 01 1970 12:00:00 AM |
| Date | Nov, 25 2010 11:56:13 AM | Nov, 25 2010 11:56:13 AM |
| String | "test1" | "test1" |
| String | null (ω) | "" (an empty string) |
| String | "test3" | "test3" |
| Boolean | true | true |
| Boolean | null (ω) | false |
| Boolean | false | false |
| List | null | [ ] (empty list) |
| List | [1,2,3] | [1,2,3] |
Example 2#
=DENULLIFY(#Column1;"123456789")
| Data Field Type | Column1 | Returns |
|---|---|---|
| Integer | null (ω) | 123456789 |
FLOAT#
Syntax#
FLOAT(<number>;<string>;<Boolean>;<date>)
Description#
Converts a number, string, Boolean, or date into a float value. This function is useful if another function's arguments require a float value. If the data contained in the strings are not integers (words, dates, IP addresses), FLOAT returns an error message.
Examples
| Data Field Type | Column1 | FLOAT Returns |
|---|---|---|
| string | 27 | 27.0 |
| string | 33 | 33.0 |
| string | 100 | 100.0 |
| Boolean | false | 0.0 |
| Boolean | true | 1.0 |
| date | Feb 23, 1990 12:00:00 AM | 635,727,600,000.0 |
| date | Jun 2, 1955 12:00:00 AM | -460,256,400,000.0 |
| number | 6 | 6.0 |
| number | 12 | 12.0 |
ISBLANK#
Syntax#
ISBLANK(<any>)
Description#
Returns a Boolean value. If the column contains an empty string, only blank spaces, or is null, it returns true. Otherwise it returns false.
Examples
| Column1 | ISBLANK returns |
|---|---|
| λ (empty string) | true |
| ω (null) | true |
| (white spaces only) | true |
| 25 | false |
| George | false |
ISEVEN#
Syntax#
ISEVEN(<integer>)
Description#
Returns a Boolean value. If number is even, it returns true. Otherwise it returns false. Please note that in Spectrum as in mathematics, zero is considered even.
Examples
| Column1 | ISEVEN returns |
|---|---|
| 2 | true |
| 3 | false |
| -2 | true |
| 0 | true |
ISNULL#
Syntax#
ISNULL(<integer>)
Description#
Returns a Boolean value. If the column contains a null, it returns true. Otherwise it returns false.
Examples
| Column1 | ISNULL returns |
|---|---|
| ω(null) | true |
| λ (empty string) | false |
| 2 | false |
ISODD#
Syntax#
ISODD(<integer>)
Description#
Returns a Boolean value. If number is odd, it returns true. Otherwise it returns false. Please note that in Spectrum as in mathematics, zero is considered even.
Examples
| Column1 | ISODD returns |
|---|---|
| 3 | true |
| 4 | false |
| -3 | true |
| 0 | false |