Using Formulas - List of Formula Functions

Formulas that can be used in Form, Memo and Action templates.

See also: Introduction to Formulas in Forms, Formula Field Referencing

New Formulas

Now available on the web, iOS, Windows and Android.

AVERAGE(arg_list)
Returns the mean of the given list of values

STDEV(arg_list)
Returns the sample standard deviation of the given list of values

NUMBERVALUE(expression)Attempts to convert the given value to a number. For a time this will be a number between 0 and 1; for a date this will be the number of days since 1900. 

TEXT(expression, [format code])Converts expression/reference into the format specified.

DAY(date)
Returns the day from a date, from 1 to 31.

MONTH(date)Returns the month from a date, represented as an integer from 1 (January) to 12 (December)

YEAR(date)Returns the year from a date, represented as an integer from 1900 to 9999.

DATE(year, month, day)
Create a date with the given year, month and day

DAYS(end_date, start_date)Returns the number of days between two dates.

WEEKDAY(date)
Returns the day of the week as an integer, from 1 (Sunday) to 7 (Saturday). 

TIME(hours, minutes, seconds)Returns a time for the given hours, minutes and seconds. minutes and seconds can be less than 1 or greater than 60, and the overflow will be added or subtracted from the result.

ROW([reference])
Returns the current row or the row of the referenced cell.

POWER(n, exponent)
Returns the result of a number raised to a power.

TEXTJOIN(delimiter, ignore_empty, arg_list)The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
Usage: TEXTJOIN(", ", TRUE, A:A). 

COUNT(arg_list)
Counts the number of fields that contain numbers, and counts numbers within the list of arguments.

COUNTA(arg_list)
Counts the number of fields that are not empty in a range.

COUNTIF(range, criterion)Count the number of fields that meet the criteria. Usage: COUNTIF(A2:A5,"Nevada") or COUNTIF(A2:A5,A4). 

COUNTBLANK(arg_list)
Counts the number of fields that are blank.

MAX(arg_list)Returns the largest value in a list of values. Empty cells, logical values, or text may be ignored.

MIN(arg_list)Returns the smallest value in a list of values. Empty cells, logical values, or text may be ignored.

PI()
Returns the mathematical constant pi to 15 significant figures.

 

Numeric Operators

add (+)
subtract (-)
multiply (*)
divide (/)

Conditional Operators

= equal to
<> not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to

Date and Time

HOUR(expression)
Returns the number of hours in the time value of an expression. For example, for 15:30, returns 15.

MINUTE(expression)
Returns the number of minutes in the time value of an expression. For example, for 15:30, returns 30. Usage: MINUTE(TOP!A).

 

Mathematical 

ABS(expression)
Returns the absolute value of a number or time. For example, ABS(-1) is equal to 1. Usage: ABS(TOP!A). 

MAX(number1,[number2],...)
Returns largest number. Can be used with ranges and lists.

MIN(number1,[number2],...)
Returns smallest number. Can be used with ranges and lists.

SUM(number1,[number2],...)
Returns sum of arguments. Can be used with ranges and lists. For example, SUM(T1!A:C) to get the total of columns A, B and C. Also note that you can do totals within tables for number and formula type fields. 

SQRT(number)
Returns the square root of a number. Use a number, e.g. SQRT(25) or a reference, e.g. SQRT(TOP!A) or SQRT(A). 

QUOTIENT(numerator, denominator)
Returns the integer (whole number) portion of a division. Use this function when you want to discard the remainder of a division. QUOTIENT(5,2) returns 2.
Usage: QUOTIENT(TOP!A, TOP!B)

MOD(number, divisor)
Returns the remainder after number is divided by divisor. The result has the same sign as divisor. MOD(3,2) is 1.

FLOOR(number, significance)
Rounds down to the nearest multiple of significance. FLOOR(3.7,2) rounds 3.7 down to 2. FLOOR(1.58,0.1) rounds 1.58 down to 1.5.  

CEILING(number, significance)
Returns number rounded up, away from zero, to the nearest multiple of significance.

TRUNC(number, [num_digits])

 

Logical 

IF(condition, result when true, result when false)
An IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

AND(expression1,[expression2],...)
Returns TRUE if all expressions evaluate as true, FALSE if at least one does not. 

OR (expression1,[expression2],...)
Returns TRUE if at least one expression evaluates as true, FALSE if all evaluate as false. 

NOT(argument)
Used to make sure one value is not equal to another - reverses the logic of its argument. 

IFERROR(value, value_if_error)
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Used to trap and handle errors in a formula.

ISNUMBER(value)
Returns TRUE if value refers to a number.

ISTEXT(value)
Returns TRUE if value refers to text.

 

Formatting and String Manipulation

TEXT(expression,[format])
Change the way a number appears by applying formatting to it with format codes. Usage: TEXT(A); TEXT(A,"HH:MM") TEXT(A,"H:MM AM/PM")

CONCAT(expression1,[expression2],...)
Joins two or more text strings into one string. For example, CONCAT("one","two" returns "onetwo".
Usage: CONCAT(TOP!A," and ",TOP!B) 

CHAR(char)
Returns selected special character. Supports characters 9, 10, 13 and 32 and above (like Excel online) up to 127

FIND(find_text, within_text, [start_num])
Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.

LEFT(text, [num_chars])
Returns a specific number of characters from a text string, starting from the first character, based on the number of characters you specify. LEFT(antiquated,4) returns "anti".
Usage: LEFT(TOP!A,#)

RIGHT(text,[num_chars])
Returns a specific number of characters from the end of a text string, based on the number of characters you specify. RIGHT(antiquated,4) returns "ated".
Usage: RIGHT(TOP!A,#)

MID(text, start_num, num_chars)
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. MID(antiquated,4,2) returns "iq".
Usage: MID(TOP!A,#,#)

LEN(text)
Returns the number of characters in a string. LEN("word") returns 4.
Usage: LEN(TOP!A), LEN(A)

NUMBERVALUE(value)
Attempts to convert the given value to a number. For time this will be the number between 0 and 1, for a date this will be the days since Jan 1 1900.

TRIM(text)
Removes all spaces from text except for single spaces between words.

ROUND(number,num_digits)
Rounds a number to a specified number of digits. ROUND(3.14159,2) returns 3.14.
Usage: ROUND(TOP!A,#)
For more information about rounding, and avoiding compounding rounding errors, see here.

 

Lookup and Reference

VLOOKUP(lookup value, "Org List Name", column number, range_lookup)
Use this to look up an item in the first column of a multi column Org List, and return what is in a subsequent column (column number) in the same row. Last parameter, "range_lookup", can be TRUE (approximate match) or FALSE (exact match).

For example, you might have form users select an Employee name, then use a VLOOKUP formula to find the employee's ID number based on their name. 

 

Note: Parameters appearing in square brackets are optional. The syntax and operation of those functions is similar to their equivalent functions in Excel.