Skip to content

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