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)

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.*

## Comments

0 comments

Please sign in to leave a comment.