Summary of Formula Functions and Operators
Classification
We have categorized functions and operators into the following six categories, with detailed descriptions.
The column of operators/functions in the table below shows the standard expressions of the functions, where certain values are replaced by English, and the corresponding explanations of the values are as follows.
string: indicates a string value text: text type value logical: the logical value number: the numeric value date: date value item: denotes an array value
where the "[ ]" symbol within the parameter means optional parameters, that is, when using the function can not fill in the values.In the array function this symbol indicates a set of data.
To understand the operators and functions, you can combine the description and examples together will be easier to understand, for example, the "=>" symbol indicates the output of the formula.
Formula operators
| Category | Name | Grammar codes and patterns | Description |
|---|---|---|---|
| Numeric | Add | + | Adds two values together. |
| Numeric | Minus | - | Subtracts two values from each other. |
| Numeric | Multiply | * | Multiply two values together. |
| Numeric | Divide | / | Divides two values. |
| String | Connector | & | Splice two text values together. |
| Logical | Greater than | > | Determines if the first value is greater than the second value. |
| Logical | Greater than or equal to | >= | Determines if the first value is greater than or equal to the second value. |
| Logical | Less than | < | Determines if the first value is less than the second value. |
| Logical | Less than or equal to | <= | Determines if the first value is less than or equal to the second value. |
| Logical | Equal to | = | Determines if the first value is equal to the second value. |
| Logical | Not equal | != | If or not the first value is equal to the second value. |
| Logical | With | && | Indicates the sum of two logical conditions. |
| Logical | Or | || | Indicates the or operation of two logical conditions. |
Click for operator explanations and scenario examples
Numeric functions
| Category | Grammar codes and patterns | Description |
|---|---|---|
| SUM() | SUM(number1, [number2, …]) | Add all values. |
| AVERAGE() | AVERAGE(number1, [number2, …]) | Returns the arithmetic mean of multiple numbers. |
| MAX() | MAX(number1, [number2, …]) | Returns the largest number of values. |
| MIN() | MIN(number1, [number2, …]) | Returns the smallest of multiple values. |
| ROUND() | ROUND(value, precision) | Rounds the value by the specified number of digits. |
| ROUNDUP() | ROUNDUP(value, precision) | Rounds the value in the direction of increasing absolute value. |
| ROUNDDOWN() | ROUNDDOWN(value, precision) | Rounds the value in the direction of decreasing absolute value. |
| CEILING() | CEILING(value, [significance]) | Rounds the value up to the nearest multiple of the specified base. |
| FLOOR() | FLOOR(value, [significance]) | Rounds the value down to the nearest multiple of the specified base. |
| EVEN() | EVEN(value) | Rounds the value to the nearest even number in the direction of increasing absolute value. |
| ODD() | ODD(value) | Rounds the value to the nearest odd number in the direction of increasing absolute value. |
| INT() | INT(value) | Rounds the value down to the nearest integer. |
| ABS() | ABS(value) | Takes the absolute value of a value. |
| SQRT() | SQRT(value) | Calculate the arithmetic square root of a value. |
| MOD() | MOD(value, divisor) | Divide two values to get the remainder. |
| POWER() | POWER(base, power) | Computes the multiplicative power of a value (base). |
| EXP() | EXP(power) | Compute the multiplication power of e. |
| LOG() | LOG(number, base=10) | Computes the logarithm of a value with the specified base. |
| VALUE() | VALUE(text) | Converts a text value to a numeric value. |
Click to see the explanation of the parameters and examples of scenarios for this class of functions
String function
| Name | Grammar codes and patterns | Description |
|---|---|---|
| CONCATENATE() | CONCATENATE(text1, [text2, …]) | Concatenates multiple text values into a single text value.(The effect is equivalent to &) |
| FIND() | FIND(stringToFind, whereToSearch,[startFromPosition]) | Find the position of the first occurrence of a specific text in the content. |
| SEARCH() | SEARCH(stringToFind, whereToSearch,[startFromPosition]) | Search for the first occurrence of a specific text in the content. |
| MID() | MID(string, whereToStart, count) | Extracts a fixed-length piece of text from a specific position in the content. |
| REPLACE() | REPLACE(string, start_character, number_of_characters, replacement) | Replace a piece of text at a specific location in the content with new text. |
| SUBSTITUTE() | SUBSTITUTE(string, old_text, new_text, [index]) | Replaces all of the text at a specific location with new content. |
| LEN() | LEN(string) | Count the length of a piece of text. |
| LEFT() | LEFT(string, howMany) | Extracts the number of characters from the beginning of the text. |
| RIGHT() | RIGHT(string, howMany) | Extracts the number of characters from the end of the text. |
| LOWER() | LOWER(string) | Converts all uppercase letters to lowercase. |
| UPPER() | UPPER(string) | Convert all lowercase letters to uppercase. |
| REPT() | REPT(string, number) | Copy the text content according to the specified number of times. |
| T() | T(value) | Determine if the content is a text value. |
| TRIM() | TRIM(string) | Clear the spaces at the beginning and end of the text. |
| ENCODE_URL_COMPONENT() | ENCODE_URL_COMPONENT(component_string) | Encodes the text into the format of a URL. |
Click to see the explanation of parameters and examples of scenarios for this class of functions
Logical functions
| Name | Grammar codes and patterns | Description |
|---|---|---|
| IF() | IF(logical, value1, value2) | Determines whether a condition is met. If it is met, the first value is returned. If not, the second value is returned. |
| SWITCH() | SWITCH(expression, [pattern, result… ],[default]) | This function is a multi branch selection function. It consists of an expression+multiple (branches+return values). If the expression is equal to a branch value, the function outputs the return value corresponding to the branch. |
| TRUE() | TRUE() | Returns the logical value True(true). |
| FALSE() | FALSE() | Return logic value false(false) |
| AND() | AND(logical1, [logical2, …]) | Returns true (true) if all values are true, false (false) otherwise. |
| OR() | OR(logical1, [logical2, …]) | Returns true (true) if either argument is true, false (false) otherwise. |
| XOR() | XOR(logical1, [logical2, …]) | Returns true (true) if an odd number of arguments are true, false (false) otherwise. |
| BLANK() | BLANK() | Represents a null value. |
| ERROR() | ERROR(message) | Displays error messages and messages within cells. |
| IS_ERROR() | IS_ERROR(expression) | Checks if an expression is running incorrectly and returns true if it is. |
| NOT() | NOT(logical) | Invert the logical condition. |
Click to see the explanation of parameters and examples of scenarios for this class of functions
Date functions
| Name | Grammar codes and patterns | Description |
|---|---|---|
| TODAY() | TODAY() | Returns today's date (year, month, day), but not the exact minute (default is 00:00:00).If you want exact timing, use the NOW() function. |
| NOW() | NOW() | Return today's date and time, will be accurate to the minute. |
| TONOW() | TONOW(date, units) | Returns the difference (no positive or negative) between the current date and the specified date. |
| FROMNOW() | FROMNOW(date, units) | Returns the difference (no positive or negative) between the current date and the specified date. |
| DATEADD() | DATEADD(date, count, units) | Add a fixed time interval to the specified date. |
| DATETIME_DIFF() | DATETIME_DIFF(date1, date2, units) | Returns the difference (positive or negative) between two dates, i.e. date1 minus date2. |
| WORKDAY() | WORKDAY(startDate, numDays, [holidays]) | Returns the date several business days after the start date. |
| WORKDAY_DIFF() | WORKDAY_DIFF(startDate, endDate, [holidays]) | Returns the number of business days between two dates (with plus or minus). |
| IS_AFTER() | IS_AFTER(date1, date2) | Compare whether date1 is later than date2, if later then return true, otherwise return false. |
| IS_BEFORE() | IS_BEFORE(date1, date2) | Compares whether date1 is earlier than date2, and returns true if earlier than, false otherwise. |
| IS_SAME() | IS_SAME(date1, date2, [units]) | Compares whether date1 is equal to date2, and returns true if it is, otherwise false. |
| DATETIME_FORMAT() | DATETIME_FORMAT(date, specified_output_format) | Format a date to text in a custom form. |
| DATETIME_PARSE() | DATETIME_PARSE(date, [input_format]) | Converts text to a structured date type. |
| DATESTR() | DATESTR(date) | Formats the date as a text in "year-month-day" format (fixed format YYYY-MM-DD) |
| TIMESTR() | TIMESTR(date) | Format the date as text in the form "hour:minute:second" (fixed format HH:mm:ss) |
| YEAR() | YEAR(date) | Returns the four-digit year of the specified date. |
| MONTH() | MONTH(date) | Returns the month of the specified date. |
| WEEKDAY() | WEEKDAY(date, [startDayOfWeek]) | Returns the day of the week for the specified date. |
| WEEKNUM() | WEEKNUM(date, [startDayOfWeek]) | Returns the week of the year for the specified date. |
| DAY() | DAY(date) | Returns the number of the month the specified date belongs to, in the format of an integer between 1 - 31. |
| HOUR() | HOUR(date) | Returns the corresponding hour of the specified date, integer between 0 (12:00 am) and 23 (11:00 pm). |
| MINUTE() | MINUTE(date) | Returns the number of minutes corresponding to the specified date, in the format of an integer between 0 and 59. |
| SECOND() | SECOND(date) | Returns the number of seconds for the specified date, integer between 0 and 59. |
| SET_LOCALE() | SET_LOCALE(date, locale_modifier) | Sets the locale for the specified date and time. |
| SET_TIMEZONE() | SET_TIMEZONE(date, [tz_identifier]) | Set a specific time zone for the specified date. |
| CREATED_TIME() | CREATED_TIME() | Returns the date and time when the record was created. |
| LAST_MODIFIED_TIME() | LAST_MODIFIED_TIME([{field1},{field2}, …]) | Returns the time when the last modification was made in each cell of the row. |
Click to see the parameters of this class and examples of scenarios
Array and other functions
| Name | Grammar codes and patterns | Description |
|---|---|---|
| COUNT() | COUNT(number1, [number2, ….]) | Statistics the number of numeric values. |
| COUNTA() | COUNTA(textOrNumber1, [textOrNumber2, …]) | Count the number of non-null values. |
| COUNTIF() | COUNTIF(values, keyword, operation) | Count the number of keyword occurrences in values. |
| COUNTALL() | COUNTALL(textOrNumber1, [textOrNumber2, …]) | Count the number of all values, including null values. |
| ARRAYCOMPACT() | ARRAYCOMPACT([item1, item2, item3]) | Removes empty strings and null values from the array. |
| ARRAYFLATTEN() | ARRAYFLATTEN([item1, item2, item3]) | Tile an array by removing any array nesting. All data becomes elements of the same array. |
| ARRAYJOIN() | ARRAYJOIN([item1, item2, item3], separator) | Concatenate an array of table summaries with a specific delimiter. |
| ARRAYUNIQUE() | ARRAYUNIQUE([item1, item2, item3]) | Returns only the unique item in the array. |
| RECORD_ID() | RECORD_ID() | Returns the ID of the current record. |
Click to see the parameter explanation and scenario examples of this type of function