Field Types Used in This Document

To learn about operators, see Using Operators.

To learn about using functions, see Using the Formula Builder. SHOW ME HOW.

To learn about dealing with date and time, see Using Date and Time patterns

Supported Functions - As Grouped in the Formula Builder

Bioinformatics

• SWSCORE Aligns two sequences by Smith-Waterman and calculates the score.

Comparison

• COMPARE Compares two values. (Returns a negative integer, zero, or a positive integer as this object is less than, equal to, or greater than the specified object.)
• EQUALS Checks to see if two values are identical.
• GE Greater equal
• GT Greater than
• LE Less equal
• LT Less than
• NOT_EQUALS Checks to see if two values are not identical.

Date and Time

• AFTER Tests if this date is after the specified date.
• ASDATE Converts a character sequence to a date using predefined (dd-MM-yyyy hh:mm:ss, dd.MM.yyyy hh:mm:ss, dd-MM-yyyy, dd.MM.yyyy) or an optional date parse pattern.
• BEFORE Tests if this date is before the specified date.
• DAY Returns only the day of the month of a date. The day is given as an integer, ranging from 1 to 31.
• FORMATDATE Converts date to string by applying a format pattern.
• HOUR Returns only the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
• JOB_STARTTIME Returns the start time of the workbook job.
• MONTH Returns only the month of a date represented by a serial number.
• MSTODATE Converts an integer representing a timestamp in milliseconds from January 1, 1970, 00:00:00 GMT to its corresponding date value.
• NOW Returns the current date and time.
• TODAY Returns the current day (time at midnight). (from version 1.1.1)
• TIMESTAMP Difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.
• YEAR Returns the year corresponding to a date. The year is returned as an integer, ranging from 1900-9999.

Encoding

• DEC2HEX Converts a decimal number to hexadecimal.
• DEC_BASE64 Decodes a base64 encoded string.
• ENC_BASE64 Encodes string as base64.
• HEX2DEC Converts a hexadecimal number given as a string to decimal number.
• IP_TO_NUMBER Converts IP4 address to decimal presentation.
• NUMBER_TO_IP Converts number to internet standard dot notation.
• TEXT2HEX Converts text into a hex string.

General

• COPY Copies values from a sheet column to a new sheet.
• DENULLIFY Converts null values to not null default value.
• FLOAT Converts a text to a float value.
• ISBLANK Returns TRUE if the value is blank.
• ISEVEN Returns TRUE if the number is even.
• ISNULL Returns TRUE if the value is null.
• ISODD Returns TRUE if the number is odd.
• N Returns a value converted to a number.

Grouping

• GROUPAVERAGE Returns the average of its arguments.
• GROUPBY Groups records.
• GROUPBYBIN Groups values into bins.
• GROUPBYGAP Groups records by maximum gap between two values in a timestamp or numerical value.
• GROUPCOMBIN Generates combinations of values in a group.
• GROUPCONCAT Concatenates all non-null values seen in a group.
• GROUPCOUNT Counts the records in one group.
• GROUPCOUNTDISTINCT Counts the distinct values in one group.
• GROUPANY Returns any random value of a group.
• GROUPFIRST () Returns the first value of a group.
• GROUPLAST () Returns the last value of a group.
• GROUPMAP Creates a JSON map based on two columns of all records within a group, keys and values.
• GROUPMAX Maximum of its arguments.
• GROUPMEDIAN Returns the median of all values in a group.
• GROUPMIN Minimum of its arguments.
• GROUPPERCENTILE Return the number which is nth percentile in this distribution.
• GROUPSTDEVP Estimates standard deviation on the entire population.
• GROUPSTDEVS Estimates standard deviation on a sample of the entire population.
• GROUPSUM Adds its arguments.
• GROUPTOPN Selects the top N values from a group.
• GROUP_DIFF Computes the difference to the previous value in a group.
• GROUP_JACCARD_DIST Computes the jaccard distance of all values in a group.
• GROUP_JSON_ARRAY Creates a JSON array of all non-null values seen in a group.
• GROUP_PAIR Generates pairs of values in a group.
• GROUP_PATH Create paths from a field (e.g. for click stream analysis).
• GROUP_PATH_CHANGES Create paths from a field, but only if the value changes (e.g. for click stream analysis).
• GROUP_PREVIOUS Returns the value of the previous record in a group.
• GROUP_SORT_ASC Groups records in sorted ascending order.
• GROUP_SORT_DESC Groups records in sorted descending order.

Logical

• AND Returns TRUE if all of its arguments are TRUE.
• IF Specifies a logical test to perform.
• NOT Reverses the logic of its argument.
• OR Returns TRUE if any argument is TRUE.

Math

• ABS Returns the absolute value of a number.
• BITAND Bitwise AND operation on given arguments.
• BITOR Bitwise OR operation on given arguments.
• CEILING Rounds a number to the nearest integer or to the nearest multiple of significance.
• COMBIN Returns the number of combinations for a given number of objects.
• COMBIND Returns the double representation of combinations for a given number of objects.
• DIFF Returns the difference of the numbers (First argument value must not be null).
• DIV Returns the quotient of the numbers.
• E Returns the value of e.
• EVEN Rounds a number up to the nearest even integer.
• EXP Returns e raised to the power of a given number.
• EXPLODE_RANGE Explodes a integer or ip address range into multiple rows. A given ip address range will converted into a range of integer before.
• FACT Returns the factorial of a number.
• FACTD Returns the double factorial of a number.
• FLOOR Rounds a number down, toward 0 (zero).
• GCD Returns the greatest common divisor.
• GEOHASH_DEC_LAT Decodes the given geohash value into a latitude with a precision of 6 after the decimal place.
• GEOHASH_DEC_LONG Decodes the given geohash value into a longitude with a precision of 6 after the decimal place.
• GEOHASH_ENC Encodes the given latitude and longitude into a geohash value with precision of 12 digits.
• INT Rounds a number down to the nearest integer.
• LCM Returns the least common multiple.
• LN Returns the natural logarithm of a number.
• LOG Returns the logarithm of a number to a specified base.
• LOG10 Returns the base-10 logarithm of a number.
• LSHIFT Shifts bits left by the given number of bits.
• MOD Returns the modula of the arguments.
• MROUND Returns a number rounded to the desired multiple
• ODD Rounds a number up to the nearest odd integer.
• PI Returns the value of pi.
• PLUS Adds its arguments (or concats string values).
• POWER Returns the result of a number raised to a power.
• PRODUCT Multiplies the arguments.
• QUOTIENT Returns the integer portion of a division.
• RAND Returns a random number between 0 (zero) and 1.
• RANDBETWEEN Returns a random number between the numbers that you specify.
• ROUND Rounds a number to a specified number of digits.
• ROUNDDOWN Rounds a number down, toward zero.
• ROUNDUP Rounds a number up, away from zero.
• RSHIFT Shifts bits right by the given number of bits.
• SIGN Returns the sign of a number.
• SQRT Returns the square root of a number.
• SQRTPI Returns the square root of (number * pi).
• SUBTRACT Substracts all values from the first argument.
• SUM Adds its arguments.
• TRUNC Truncates a number to an integer or truncates its decimal places.
• URSHIFT The unsigned right shift operator shifts a zero into the leftmost position.

Statistical

• AVERAGE Returns the average of its arguments.
• MAX Returns maximum of arguments.
• MIN Returns minimum of arguments.
• STDEVS Estimates standard deviation on a sample of the entire population.
• STDEVP Estimates standard deviation on the entire population.

Text

• CHAR Returns the character specified by the codepoint number.
• CODE Returns a numeric code for the first character in a text string
• CONCAT Concatenates several text items into one text item.
• CONTAINS Returns whether text contains the second argument.
• CONTAINS_IC Returns whether text contains the second argument. (not case sensitive)
• ENDSWITH Tests if this string ends with the specified suffix.
• INDEX Finds the first occurence of the given regular expression.
• JSON_ELEMENT Returns the given element of a json array.
• JSON_ELEMENTS Returns all elements of a json array.
• JSON_KEYS Returns all keys from a json string.
• JSON_MAP Creates a JSON map from two JSON arrays.
• JSON_VALUE Returns a value for a given key from a json object.
• LEFTReturns the leftmost characters from a text value.
• LEN Returns the number of characters in a text string.
• LOWER Converts text to lowercase.
• MATCHES Return true if a string matches a regular expression.
• NGRAM Returns subsequences of N items of a given text.
• PROPER Capitalizes the first letter in each word of a text value.
• REGEX Replaces characters within a text string which matches a regular expression with a different text string..
• REGEXTRACT Extracts tokens that match a regular expression.
• REPLACE Replaces characters within a text string with a different text string.
• REPLACEALL Replaces each substring of this string that matches the given regular expression with the given replacement.
• REPT Repeats text a given number of times.
• RIGHT Returns the rightmost characters from a text value.
• STARTSWITH Tests if this string starts with the specified prefix.
• SUBSTITUTEALL Substitutes new text for all occurencies old text in a text string.
• SUBSTITUTEFIRST Substitutes new text for first occurency old text in a text string.
• SUBSTR Returns a specific number of characters from a text string starting at the position that you specify.
• T Converts its arguments to text.
• TOKENIZE Tokenizes text.
• TRIM Removes spaces from text.
• UPPER Converts text to uppercase.

Trigonometry

• ACOS Returns the arccosine of a number.
• ACOSH Returns the inverse hyperbolic cosine of a number.
• ASIN Returns the arc sine of a number.
• ASINH Returns the inverse hyperbolic sine of a number.
• ATAN Returns the arc tangent of a number.
• ATAN2 Returns the arctangent from x- and y-coordinates.
• ATANH Returns the inverse hyperbolic tangent of a number.
• COS Returns the cosine of a number.
• COSH Returns the hyperbolic cosine of a number.
• COT Returns the cotangent of a number.
• DEGREES Converts radians to degrees.
• RADIANS Converts degrees to radians.
• SIN Returns the sine of a number.
• SINH Returns the hyperbolic sine of a number.
• TAN Returns the tangent of a number.
• TANH Returns the hyperbolic tangent of a number.

URL

• URL_AUTHORITY Gets the authority part of the URL provided as string.
• URL_DEFAULT_PORT Gets the default port number of the protocol associated with this URL.
• URL_FILE Gets the file name of the URL provided as string.
• URL_HOST Gets the host name of the URL provided as string.
• URL_PARAM Gets a value addressed by a param of the URL provided as string.
• URL_PARAMS Gets the values addressed by a param of the URL provided as list.
• URL_PATH Gets the path part of the URL provided as string.
• URL_PLD Extract the PLD (paid-level domain, as per the IRLbot paper) from a url.
• URL_PORT Gets the port number of the URL provided as long.
• URL_PROTOCOL Gets the protocol name of the URL provided as string.
• URL_QUERY Gets the query part of the URL provided as string.
• URL_REF Gets the anchor ('reference') of the URL provided as string.
• URL_TLD Gets the TLD of an URL.
• URL_USERINFO Gets the userinfo part of the URL provided as string.