List of available functions

Function Description Usage example
ABS(number) absolute ABS(-1337) = 1,337
ACOS(number) arc cosine ACOS(0.785) = 0.667
AFTER(start date; seconds) date after a number of seconds has passed after date AFTER(@514,561,449.039;3,600) = 22 apr. 2017 16:44:09
AND(boolean; boolean; ...) and AND(FALSE;TRUE) = FALSE
APPEND(list; second; ...) append value APPEND({1;2;3;4};5) = {5}
APPEND.LIST(first; second; ...) append list APPEND.LIST({1;2;3;4};{5;6;7;8}) = {8}
ASIN(number) arc sine ASIN(0.785) = 0.903
ATAN(number) arc tangens ATAN(0.785) = 0.666
AVERAGE(value; value; value; value; ...) average AVERAGE(1;2;3;3) = 2.25
BASE64.DECODE(data) decode base64 BASE64.DECODE("SGVsbG8gd29ybGQh") = [12 bytes]
BASE64.ENCODE(data) encode base64 BASE64.ENCODE(`U0dWc2JHOGdkMjl5YkdRaA==`) = U0dWc2JHOGdkMjl5YkdRaA==
CEILING(number) round up to integer CEILING(3.134) = 4
CHOOSE(index; value; value; value; value; ...) choose CHOOSE(2;"horse";"correct";"battery";"staple") = correct
CONCAT(text; text; ...) concatenate CONCAT("foo";"bar") = foobar
COS(number) cose COS(0.785) = 0.707
COSH(number) cosine hyperbolic COSH(0.785) = 1.325
COUNT(value; value; value; value; ...) number of numeric values COUNT(1;2;3;3) = 4
COUNTA(value; value; value; value; ...) number of items COUNTA(1;2;3;3) = 4
DATE.UTC(year; month; day) make a date (in UTC) DATE.UTC(1988;8;11) = 11 aug. 1988 02:00:00
DAY.UTC(date) day in month (in UTC) of date DAY.UTC(@514,561,449.037) = 22
DECODE(data; encoding) decode text DECODE(`//5IAGUAbABsAG8AIAB3AG8AcgBsAGQAIQA=`;"UTF-16") = Hello world!
DURATION(start date; end date) number of seconds that passed between dates DURATION(@0;@514,561,449.039) = 514,561,449.039
ENCODE(text; encoding) encode text ENCODE("Hello world!";"UTF-16") = [26 bytes]
ENCODEURL(text) url encode ENCODEURL("warp [core]") = warp%20%5Bcore%5D
EXP(number) e^ EXP(0.785) = 2.193
FLOOR(number) round down to integer FLOOR(3.134) = 3
FROM.EXCELDATE(Excel timestamp) from Excel timestamp FROM.EXCELDATE(42,847.572) = 22 apr. 2017 15:44:09
FROM.ISO8601(UNIX timestamp) interpret ISO-8601 formatted date FROM.ISO8601("2017-04-22T15:44:09+02:00") = 22 apr. 2017 15:44:09
FROM.JSON(JSON) read JSON value FROM.JSON("[1,2,3]") = {3}
FROM.UNIX(UNIX timestamp) interpret UNIX timestamp FROM.UNIX(1,492,868,649.034) = 22 apr. 2017 15:44:09
GLUE(list; glue) glue list GLUE({1;2;3;4};"~") = 1~2~3~4
HEX.DECODE(data) decode hex HEX.DECODE("SGVsbG8gd29ybGQh") = [8 bytes]
HEX.ENCODE(data) encode hex HEX.ENCODE(`U0dWc2JHOGdkMjl5YkdRaA==`) = 53475673624738676432397962475168
HILBERT.D(n; x; y) to Hilbert index HILBERT.D(1024;100;50) = 3,145,728
HILBERT.X(n; d) Hilbert index to X HILBERT.X(1024;100) = 4
HILBERT.Y(n; d) Hilbert index to Y HILBERT.Y(1024;100) = 14
HOUR.UTC(date) hour (in UTC) of time HOUR.UTC(@514,561,449.038) = 13
IF(boolean; value if true; value if false) if IF(FALSE;"yes";"no") = no
IFERROR(value; value if error) if error IFERROR(1346;"(error)") = 1,346
IN(value; value; value; value; value; ...) contains IN("horse";"correct";"battery";"horse";"staple") = TRUE
ITEMS(list) number of items ITEMS({"correct";"horse";"battery";"staple"}) = 4
LARGE(value; value; value; value; ...) highest LARGE(1;2;3;3) = 3
LEFT(text; index) leftmost characters LEFT("john doe";3) = joh
LENGTH(text) length of text LENGTH("john doe") = 8
LIST(element1; element2; elementN; ...) list {1;2;3} = {3}
LN(number) natural logarithm LN(0.785) = -0.242
LOG(number; ...) logarithm LOG(0.785) = -0.105
LOWER(text) lowercase LOWER("FOO") = foo
MEDIAN(value; value; value; value; ...) median value (average in case of a draw) MEDIAN(1;2;3;3) = 2.5
MEDIAN.HIGH(value; value; value; value; ...) median value (highest in case of a draw) MEDIAN.HIGH(1;2;3;3) = 3
MEDIAN.LOW(value; value; value; value; ...) median value (lowest in case of a draw) MEDIAN.LOW(1;2;3;3) = 2
MEDIAN.PACK(value; value; value; value; ...) median value (pack in case of a draw) MEDIAN.PACK(1;2;3;3) = 2,3
MID(text; index; length) substring MID("john doe";5;3) = doe
MIN(value; value; value; value; ...) lowest MIN(1;2;3;3) = 1
MINUTE.UTC(date) minute (in UTC) of time MINUTE.UTC(@514,561,449.039) = 44
MONTH.UTC(date) month (in UTC) of MONTH.UTC(@514,561,449.038) = 4
NEGATE(number) - NEGATE(-1337) = 1,337
NORM.INV(p; mu; sigma) inverse normal NORM.INV(0.5;10;1) = 10
NOT(boolean) not NOT(FALSE) = TRUE
NOT.IN(value; value; value; value; value; ...) does not contain NOT.IN("horse";"correct";"battery";"horse";"staple") = FALSE
NOW() current time NOW() = 22 apr. 2017 15:44:09
OR(boolean; boolean; ...) or OR(FALSE;TRUE) = TRUE
PACK(list) list to pack PACK({1;2;3;4}) = 1,2,3,4
PACK.VALUES(value; value; value; value; ...) pack PACK.VALUES("horse";"correct";"battery";"staple") = horse,correct,battery,staple
POWER(base; exponent) to the power POWER(2;32) = 4,294,967,296
POWER.DOWN(n; base) to lower power of POWER.DOWN(510;2) = 256
POWER.UP(n; base) to upper power of POWER.UP(510;2) = 512
PROPER(text) capitalize PROPER("john doe") = John Doe
RAND() random number between 0 and 1 RAND() = 0.446
RANDBETWEEN(lower bound; upper bound) random number between RANDBETWEEN(0;100) = 25
RANDSTRING(pattern) random string with pattern RANDSTRING("[0-9]{4}[A-Z]{2}") = 0543QF
READ.DATE(text; format) read date in format READ.DATE("1988-08-11";"yyyy-MM-dd") = 11 aug. 1988 02:00:00
READ.NUMBER(text; decimal separator; thousands separator; ...) read number READ.NUMBER("1.337,12";",";".") = 1,337.12
REPLACE(text; find; replacement) substitute REPLACE("warpcore";"warp";"transwarp") = transwarpcore
REPLACE.PATTERN(text; find; replacement) replace using pattern REPLACE.PATTERN("1337AB";"[0-9]+";"#") = #AB
RIGHT(text; index) rightmost characters RIGHT("john doe";3) = doe
ROUND(number; decimals; ...) round ROUND(3.134;2) = 3.13
SECOND.UTC(date) seconds (in UTC) of time SECOND.UTC(@514,561,449.039) = 9
SIGN(number) sign SIGN(-1337) = -1
SIMILARITY(text; text) text similarity SIMILARITY("warp";"warpcore") = 4
SIN(number) sine SIN(0.785) = 0.707
SINH(number) sine hyperbolic SINH(0.785) = 0.869
SIZE.OF(data) number of bytes SIZE.OF(`//5IAGUAbABsAG8AIAB3AG8AcgBsAGQAIQA=`) = 26
SPLIT(text; separator) split SPLIT("1337AB#12#C";"#") = {3}
SQRT(number) square root SQRT(144) = 12
STDEV.P(value; value; value; value; ...) standard deviation (of population) STDEV.P(1;2;3;3) = 0.829
STDEV.S(value; value; value; value; ...) standard deviation (of sample) STDEV.S(1;2;3;3) = 0.957
SUM(value; value; value; value; ...) sum SUM(1;2;3;3) = 9
TAN(number) tangens TAN(0.785) = 1
TANH(number) tangens hyperbolic TANH(0.785) = 0.656
TO.EXCELDATE(date) to Excel timestamp TO.EXCELDATE(@514,561,449.036) = 42,847.572
TO.ISO8601(date) to ISO-8601 formatted date in local timezone TO.ISO8601(@514,561,449.035) = 2017-04-22T15:44:09+02:00
TO.ISO8601.UTC(date) to ISO-8601 formatted date in UTC TO.ISO8601.UTC(@514,561,449.035) = 2017-04-22T13:44:09Z
TO.JSON(list) encode JSON TO.JSON({1;2;3;4}) = [1,2,3,4]
TO.UNIX(date) to UNIX timestamp TO.UNIX(@514,561,449.034) = 1,492,868,649.034
TRIM(text) trim spaces TRIM(" warp core ") = warp core
UNPACK(pack) pack to list UNPACK("1,2,3,4") = {4}
UPPER(text) uppercase UPPER("foo") = FOO
UUID() generate UUID UUID() = D2BE19E9-F5D3-4C81-8FB1-8DEE47EB71BF
VAR.P(value; value; value; value; ...) variance (of population) VAR.P(1;2;3;3) = 0.688
VAR.S(value; value; value; value; ...) variance (of sample) VAR.S(1;2;3;3) = 0.917
WRITE.DATE(date; format) write date in format WRITE.DATE(@514,561,449.045;"yyyy-MM-dd") = 2017-04-22
XOR(boolean; boolean) xor XOR(FALSE;TRUE) = TRUE
YEAR.UTC(date) year (in UTC) of date YEAR.UTC(@514,561,449.038) = 2,017