Math Functions#
Elementary math functions allow you to complete calculations, such as determining the greatest common denominator, returning the value of pi, and finding the square root of your data.
These are the math functions in Spectrum:
ABS#
Syntax#
ABS(<number>)
Description#
Returns the absolute value of a number.
Examples
| Value | ABS() returns |
|---|---|
| 123.987 | 123.987 |
| -123.987 | 123.987 |
| 123 | 123 |
| -123 | 123 |
BIGDECIMAL#
Syntax#
BIGDECIMAL(<number>)
Description#
Converts the values of a column with a number data type to the big decimal data type.
Examples
| Field type | Number | BIGDECIMAL returns |
|---|---|---|
| Integer | 10 | 10 |
| Integer | -345352 | -345352 |
| Big Integer | 10,223,372,036,854,775,458,938,943 | 10,223,372,036,854,775,458,938,943 |
| Big Integer | -1,329,223,372,036,854,775,807,934 | -1,329,223,372,036,854,775,807,934 |
| Float | 10.3 | 10.3 |
| Float | 33.333333333 | 33.333333333 |
BIGINTEGER#
Syntax#
BIGINTEGER(<number>)
Description#
Converts the values of a column with a number data type to the big integer data type. If the number being converted has a value after the decimal, the result is rounded down.
Examples
| Field type | Number | BIGINTEGER returns |
|---|---|---|
| Integer | 10 | 10 |
| Integer | -345352 | -345352 |
| Big Float | 10.223,372,036,854,775,458,938,943 | 10 |
| Big Float | -9.329,223,372,036,854,775,807,934 | -10 |
| Float | 10.3 | 10 |
| Float | 33.999999 | 33 |
| Float | -2.6 | -3 |
BITAND#
Syntax#
BITAND(<integer>;<integer>; ...)
Description#
Bitwise AND operation on given integer arguments. The function converts the integers to binary and returns 1 in each bit position where all input arguments have a one in the corresponding position and zero in all others.
This function supports more than 255 arguments.
Examples
| Column1 | Column2 | Column3 | BITAND() returns |
|---|---|---|---|
| 2 | 3 | 6 | 2 |
| (0010) | (0011) | (0110) | = 0010 |
| Column1 | Column2 | Column3 | BITAND() returns |
|---|---|---|---|
| 4 | 8 | 12 | 0 |
| (0100) | (1000) | (1100) | = 0000 |
| Column1 | Column2 | Column3 | BITAND() returns |
|---|---|---|---|
| 23 | 8 | 109 | 5 |
| (0010111) | (0111101) | (1101101) | = 0000101 |
BITOR#
Syntax#
BITOR(<integer>, <integer>; ...)
Description#
Bitwise OR operation on given integer arguments. The function converts the integers to binary and returns 1 in each bit position where any input arguments have a one in the corresponding position and zero in all others.
This function supports more than 255 arguments.
Examples
| "Column1","Column2","Column3","BITAND() | returns" |
|---|---|
| "2","3","6","7" | |
| "(0010)","(0011)","(0110)","= | 0111" |
| " |
| "Column1","Column2","Column3","BITAND() | returns" |
|---|---|
| "4","8","12","12" | |
| "(0100)","(1000)","(1100)","= | 1100" |
| " |
| Column1 | Column2 | Column3 | BITAND() returns |
|---|---|---|---|
| 23 | 8 | 109 | 127 |
| (0010111) | (0111101) | (1101101) | = 1111111 |
CEILING#
Syntax#
CEILING(<number>;<number>)
Description#
Rounds a number up to the nearest integer or to the nearest multiple of significance.
Examples
| Value | CEILING() returns |
|---|---|
| 3 | 0 |
| 3.1 | 4 |
| 8.94 | 9 |
COMBIN#
Syntax#
COMBIN(<number>;<number>)
Description#
Returns the integer representation of possible combinations for a given number of objects.
Examples
| Set size | Number of objects | COMBIN() returns |
|---|---|---|
| 10 | 2 | 45 |
| 10 | 3 | 120 |
| 10 | 5 | 252 |
| 5 | 10 | <error> |
COMBIND#
Syntax#
COMBIND(<number>;<number>)
Description#
Returns the double (or float) representation of possible combinations for a given number of objects.
Examples
| Set size | Number of objects | COMBIND() returns |
|---|---|---|
| 10.5 | 8.3 | 45 |
| 123.45 | 12.34 | 14,383,566,047,438,588 |
| 12.38 | 15.56 | <error> |
CONVERT#
Syntax#
CONVERT(<number>;<string,measurement abbreviation>;<string,measurement abbreviation>)
Description#
Converts a number from one measurement to another measurement.
The following measurements are available for the CONVERT function:
| Measurement | Measurement abbreviation |
|---|---|
| Millisecond | ms |
| Second | s |
| Minute | m |
| Hour | h |
| Day | d |
| Year | y |
Examples
| Number | From | To | Returns |
|---|---|---|---|
| 1000 | ms | s | 1 |
| 1000 | h | m | 60000 |
| 1000 | d | y | 2.737850787132101 |
| 5000 | h | d | 208.3333333333334 |
DIFF#
Syntax#
DIFF(<number>;<number>)
Description#
Returns the difference of thx numbers. (First argument value must not be null.)
Examples
| Column1 | Column2 | DIFF() returns |
|---|---|---|
| 5 | 2 | 3 |
| 2 | 5 | -3 |
| 10.5 | 7 | 3.5 |
DIV#
Syntax#
DIV(<number>; <number>; ...)
Description#
Returns the quotient of the numbers. This function supports more than 255 arguments.
Examples
| Column1 | Column2 | DIV() returns |
|---|---|---|
| 6 | 3 | 2 |
| 3 | 6 | 0.5 |
| -6 | 2 | -3 |
| 2 | 0 | <error> |
E#
Syntax#
E()
Description#
Returns the value of the mathematical constant e. The numerical value of e truncated to 15 decimal places is 2.718281828459045.
Example
| E() returns |
|---|
| 2.718281828459045 |
EVEN#
Syntax#
EVEN(<number>)
Description#
Rounds a number up to the nearest even integer.
Examples
| Column1 | EVEN() returns |
|---|---|
| 4.5 | 6 |
| 5.5 | 6 |
| 6 | 6 |
| -7.33 | -8 |
EXP#
Syntax#
EXP(<number>)
Description#
Returns e raised to the power of a given number.
Examples
| Column1 | EXP() returns |
|---|---|
| 3 | [20.085536923187668] |
| 12 | [162,754.79141900392] |
| -5 | [0.006737946999085467 ] |
EXPLODE_RANGE#
Syntax#
EXPLODE_RANGE(<integer>;<integer>)\
EXPLODE_RANGE(<sting>;<string>)
Description#
Explodes a integer or IP address range into multiple rows. An IP address range is first converted to a range of integers. The range length is limited to 100000. All arguments must be of the same type.
Example
| Column1 | Column2 | EXPLODE_RANGE() returns |
|---|---|---|
| 1 | 5 | 1 |
| 7 | 3 | 2 |
| 3 | ||
| 4 | ||
| 5 | ||
| 7 | ||
| 6 | ||
| 5 | ||
| 4 | ||
| 3 |
FACT#
Syntax#
FACT(<number>)
Description#
Returns the factorial of a number (0 <= number < 21).
Examples
| Column1 | FACT() returns |
|---|---|
| 3 | 6 |
| 4 | 24 |
| 8 | [40,320] |
| 21 | <error> |
| -3 | <error> |
FACTD#
Syntax#
FACTD(<number>)
Description#
Returns the double factorial of a number (0 <= number < 21).
Examples
| Column1 | FACTD() returns |
|---|---|
| 3 | 6 |
| 4 | 24 |
| 8 | [40,320] |
| 21 | <error> |
| -3 | <error> |
FLOOR#
Syntax#
FLOOR(<number>;<number>)
Description#
Rounds a number down, toward 0 (zero). The second value is the significance.
Examples
| Column1 | FLOOR() returns |
|---|---|
| 123.45 | 123 |
| 37.888 | 37 |
| -5.5 | -6 |
Add a significance value to round down from a specific decimal position.
| Column1 | FLOOR(<column1>;3) |
|---|---|
| 123.456789 | 123.456 |
GCD#
Syntax#
GCD(<number>;<number>;...)
Description#
Returns the greatest common divisor, which is an integer that divides into all of the numbers without a remainder.
Examples
| Column1 | Column2 | GCD() returns |
|---|---|---|
| 5 | 2 | 1 |
| 24 | 36 | 12 |
| 20.8 | 12 | 4 |
| 20 | -30 | 5 |
GEOHASH_DEC_LAT#
Syntax#
GEOHASH_DEC_LAT(<string>)
Description#
Decodes the given geohash value into the latitude with a precision of six after the decimal place.
The purpose of the geohash.org service is to offer short URLs which uniquely identify positions on the Earth, so that referencing them in emails, forums, or websites is more convenient.
Example
| Column1 | GEOHASH_DEC_LAT() returns |
|---|---|
| u4pruydqqvj | 57.649111 |
GEOHASH_DEC_LONG#
Syntax#
GEOHASH_DEC_LONG(<string>)
Description#
Decodes the given geohash value into the longitude with a precision of 6 after the decimal place.
The purpose of the geohash.org service is to offer short URLs which uniquely identify positions on the Earth, so that referencing them in emails, forums, or websites is more convenient.
Example
| Column1 | GEOHASH_DEC_LONG returns |
|---|---|
| u4pruydqqvj | 10.40744 |
GEOHASH_ENC#
Syntax#
GEOHASH_ENC(<number>;<number>)
Description#
Encodes the given latitude and longitude into a geohash value with precision of 12 digits.
The purpose of the geohash.org service is to offer short URLs which uniquely identify positions on the Earth, so that referencing them in emails, forums, or websites is more convenient.
Example
| Column1 | GEOHASH_ENC() returns |
|---|---|
| 57.64911;10.40744 | u4pruydqqvj |
INT#
Syntax#
INT(<number, string, boolean, or date>)
Description#
Rounds a number down to the nearest integer. INT accepts null values.
Converts integers contained in a string, Boolean values, or dates into the integer data field type. When using Boolean values, true is converted into 1, and false is converted into 0. Dates are converted into milliseconds (returning the difference between the current time and midnight, January 1, 1970 UTC).
Examples
| Data field type | Column1 | INT() returns |
|---|---|---|
| float | 4.7 | 4 |
| float | -4.7 | -5 |
| string | 55 | 55 |
| string | 55.2 | <error> |
| Boolean | true | 1 |
| Boolean | false | 0 |
| date | Oct 15, 2010 04:34:34 AM | 1,287,110,074,000 |
| date | Sep 25, 2010 12:33:25 PM | 1,285,410,805,000 |
| big_integer | 9,999,999,999,999,999,999 | <error> |
LCM#
Syntax#
LCM(<number>;<number>;...)
Description#
Returns the least common multiple which is the smallest positive integer that is a multiple of all integer arguments. This function supports more than 255 arguments.
Examples
| Column1 | Column2 | LCM returns |
|---|---|---|
| 5 | 2 | 10 |
| 24 | 36 | 72 |
| 20.8 | 12 | 60 |
| 20 | -30 | 60 |
LN#
Syntax#
LN(<number>)
Description#
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Example
| Column1 | LN() returns |
|---|---|
| 86 | 4.454347296253507 |
LOG#
Syntax#
LOG(<number>;<number>)
Description#
Returns the logarithm of a number to a specified base. The first number must be greater than zero and the second number is the optional base. If the base is not provided, the function uses base e (Euler's number).
Examples
| Column1 | LOG() returns |
|---|---|
| 20 | [2.995732273553991] |
| Column1 | LOG(<column1>;3) returns |
|---|---|
| 20 | [2.7268330278608417] |
LOG10#
Syntax#
LOG10(<number>)
Description#
Returns the base-10 logarithm of a number.
Example
| Column1 | LOG10() returns |
|---|---|
| 20 | 1.301029996 |
LSHIFT#
Syntax#
LSHIFT(<number>;<number>)
Description#
Shifts bits left by the given number of bits. Both values must be integers.
Example
| Column1 | Column2 | LSHIFT() returns |
|---|---|---|
| 20 | 3 | 160 |
MOD#
Syntax#
MOD(<number>;<number>;...)
Description#
Returns the modulo (remainder) of the arguments. Supports more than 255 arguments.
Examples
| Column1 | Column2 | MOD() returns |
|---|---|---|
| 19 | 17 | 2 |
| 34 | 17 | 0 |
MROUND#
Syntax#
MROUND(<number>;<number>)
Description#
Returns a number rounded to the desired multiple.
MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.
Example
=MROUND(19.4445; 2) returns 20 because 19.4445 is closer to 20 than it is to 18.
=MROUND(0.7651; 1) returns 1 because 0.7651 is closer to 1 than it is to 0.
| Value | Rounding multiple | Result |
|---|---|---|
| 0.4 | 1 | 0 |
| 0.5 | 1 | 0 |
| 0.6 | 1 | 1 |
| 8 | 3 | 9 |
| 9 | 3 | 9 |
| 10 | 3 | 9 |
| 11 | 3 | 12 |
| 12 | 3 | 12 |
| 13 | 3 | 12 |
| 1234 | 100 | 1200 |
| 1250 | 100 | 1200 |
| 1251 | 100 | 1300 |
| 1299 | 100 | 1300 |
ODD#
Syntax#
ODD(<number>)
Description#
Rounds a number up to the nearest odd integer.
Examples
| Column1 | ODD() returns |
|---|---|
| 19.4445 | 21 |
| 21.21 | 23 |
| -6.5 | -7 |
PI#
Syntax#
PI()
Description#
Returns the value of π (Pi). The numerical value of π is 3.141592654...
Example
| PI() returns |
|---|
| 3.141592653589793 |
PLUS#
Syntax#
PLUS(<any>;<any>;...)
PLUS() doesn't accept the boolean field type.
Description#
Adds its arguments (or concatenates string values). Arguments must be of the same type. Supports more than 255 arguments.
Examples
| Data type | Column1 | Column2 | Returns |
|---|---|---|---|
| number | 1 | 2 | 3 |
| string | The | Best | TheBest |
| date | 11:12:54 | 10000 | 11:14:34 |
| list | [1, 2] | [3,4 ] | [1,2,3,4] |
POWER#
Syntax#
POWER(<number>;<number>)
Description#
Returns the result of a number raised to a power.
Examples
| Column1 (base) | Column2 (power) | POWER() returns |
|---|---|---|
| 3 | 4 | 81 |
| 3 | 4.5 | 140.2961154 |
| 3 | -4 | [0.012345679012345678 ] |
PRODUCT#
Syntax#
PRODUCT(<number>;<number>;...)
Description#
Multiplies the arguments. Supports more than 255 arguments.
Examples
| Column1 | Column2 | Column3 | PRODUCT() returns |
|---|---|---|---|
| 3 | 4 | 12 | |
| 3 | 4 | 4.5 | 54 |
| 3 | -4 | 4.5 | -54 |
QUOTIENT#
Syntax#
QUOTIENT(<number>;<number>)
Description#
Returns the integer portion of a division.
The returned value is the integer type. If the number exceeds the length of an integer an <error> is displayed.
Example
| Column1 | Column2 | QUOTIENT() returns |
|---|---|---|
| 9.516 | 4 | 2 |
RAND#
Syntax#
RAND()
Description#
Returns a random number between 0 (zero) and 1.
Examples
| RAND() returns |
|---|
| 0.46961970037566425 |
| [0.9695914655550188] |
| 0.9338627890583037 |
RANDBETWEEN#
Syntax#
RANDBETWEEN(<number>;<number>)
Description#
Returns a random number between the numbers that you specify.
Examples
| Column1 | Column2 | RANDBETWEEN() returns |
|---|---|---|
| 4 | 5 | [4.7091995504673365] |
| 5.52674167149901 | ||
| -1.1110290234600715 |
RANDGAUSSIAN#
Syntax#
RANDGAUSSIAN()
Description#
This function generates normal distributed random values with mean of 0 and standard deviation of 1.
For each line in the current worksheet, a normally distributed random number is returned based on the Gaussian function.
Example
Column A: fx=EXPLODE_RANGE(1;10000)
| A |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| ... 1000 |
Column B: fx=RANDGAUSSIAN()
| A | B |
|---|---|
| 1 | -0.016375461 |
| 2 | -0.474685504 |
| 3 | 1.2762378257 |
| 4 | 1.3304408702 |
| 5 | -0.172384979 |
| 6 | 1.5170906593 |
| 7 | 0.8719007714 |
| ....1000 | ... |
Histogram of results from Flip Side sheet.
ROUND#
Syntax#
ROUND(<number>)
Description#
Returns the closest integer to the argument.
Examples
| Column1 | ROUND() returns |
|---|---|
| 5.4 | 5 |
| 5.5 | 6 |
| -5.5 | -6 |
ROUNDDOWN#
Syntax#
ROUNDDOWN(<number>;<number>)
Description#
Rounds a number down toward the nearest whole number. The count is the number of digits where to start the rounding.
Examples
| Column1 | ROUNDDOWN() returns |
|---|---|
| 5.3 | 5 |
| 7.9345 | 7 |
| Column1 | Count | ROUNDDOWN(<Column1>;3) |
|---|---|---|
| 7.987654321 | 3 | 7.987 |
ROUNDUP#
Syntax#
ROUNDUP(<number>[;<number>])
Description#
Rounds a number up toward the nearest whole number. The count is the number of digits (defaults to 0).
Examples
| Column1 | ROUNDUP() returns |
|---|---|
| 5.3 | 6 |
| 7.9345 | 8 |
| Column1 | Count | ROUNDUP(\<Column1>;3) returns |
|---|---|---|
| 7.987654321 | 3 | 7.988 |
RSHIFT#
Syntax#
RSHIFT(<number>;<number>)
Description#
Shifts bits right by the given number of bits. Both values must be integers.
Examples
| Column1 | Column2 | RSHIFT() returns |
|---|---|---|
| 2000 | 3 | 250 |
| 500 | 7 | 3 |
SIGN#
Syntax#
SIGN(<number>)
Description#
Returns the sign of a number as integer.
Examples
| Column1 | SIGN() returns |
|---|---|
| 9.516 | 1 |
| 0 | 0 |
| -9.516 | -1 |
SQRT#
Syntax#
SQRT(<number>)
Description#
Returns the square root of number. Number must be positive.
Examples
| Column1 | SQRT() returns |
|---|---|
| 25 | |
| 33.6 | 5.796550698 |
| -49 | <error> |
SQRTPI#
Syntax#
SQRTPI(<number>)
Description#
Returns the square root of (number * π). Number must be positive.
Examples
| Column1 | SQRTPI() returns |
|---|---|
| 1.7724538509055159 | |
| 4.854064781389248 | |
| -7.5 | <error> |
SUBTRACT#
Syntax#
SUBTRACT(<number>;<number>;...)
Description#
Subtracts all values from the first argument.
Examples
| Column1 | Column2 | SUBTRACT() returns |
|---|---|---|
| 5 | 2 | 3 |
| 2 | 5 | -3 |
| -3 | -7 | 4 |
| Column1 | Column2 | Column3 | SUBTRACT() returns |
|---|---|---|---|
| 30 | 5 | 8 | 17 |
SUM#
Syntax#
SUM(<number>;<number>;...)
Description#
Adds the arguments.
Examples
| Column1 | Column2 | SUM() returns |
|---|---|---|
| 2 | 3 | 5 |
| 3.3 | 2.2 | 5.5 |
| -7 | 2 | -5 |
| Column1 | Column2 | Column3 | SUM() returns |
|---|---|---|---|
| 4 | 5 | 6 | 15 |
TRUNC#
Syntax#
TRUNC(<number>)
Description#
Truncates a number to an integer or truncates its decimal places.
Examples
| Column1 | TRUNC() returns |
|---|---|
| 9.516 | 9 |
| -9.516 | -9 |
URSHIFT#
Syntax#
URSHIFT(<number>;<number>)
Description#
The unsigned right shift operator shifts a zero into the leftmost position.
Examples
| Column1 | Column2 | URSHIFT returns |
|---|---|---|
| -1 | 2 | [4,611,686,018,427,387,903] |
| 5 | -20 | 0 |
| -20 | -10 | 1,023 |
| 32 | 3 | 4 |
| 5 | 28 | 0 |