When you insert a
Formula
control on a form in
Design
mode, you can use the following formula functions.

- Absolute value
- Changes all negative numbers to positive numbers. Positive numbers and
zero are unchanged.
*Example*:`ABS(-5)`

returns 5. - Divide
- Calculates the division rounded down to the nearest integer.
*Example*:`DIV(33, 2)`

returns 16. - Exponential
- Calculates e raised to a power, where e is the constant 2.718281.
*Example*:`EXP(2)`

returns 7.39. - Factorial
- Calculates the factorial of a value.
*Example*:`FACT(6)`

returns 720. - Inverse
- Calculates the inverse (reciprocal) of a value.
*Example*:`INV(4)`

returns 0.25. - Log base 10
- Calculates logarithms to the base 10. Log base 10 is defined only for
positive numbers. When you multiply a number by 10, you increase its log by 1;
when you divide a number by 10, you decrease its log by 1.
*Example*:`LOGTEN(10000)`

returns 4. - Logarithm
- Calculates logarithm to a base that you specify.
*Example*:`LOG(7.38905)`

returns 2. - Natural log(log base e)
- Calculates logarithms to the base e.
*Example*:`LN(7.38905)`

returns 2. - Partial product
- Calculates the product of rows starting at row 1 to the current row.
*Example*: Set up a data-entry table with two columns. Column 1 contains the data values, and column 2 contains the formula`PARP(Control1)`

.Column 1 Column 2 3 3 4 12 5 60 - Partial sum
- Calculates the sum of rows starting at row 1 to the current row.
*Example*: Set up a data-entry table with two columns. Column 1 contains the data values, and Column 2 contains the formula`PARS(Control1)`

.Column 1 Column 2 1 1 4 5 5 10 - Power
- Calculates a number raised to a power.
*Example*:`POWER(10,2)`

raises 10 to the second power, which equals 100. - Product
- Calculates the product of a series of numbers.
*Example*: If Control1 contains 2, 3, and 4,`PRODUCT(Control1)`

returns 24=(2 * 3 * 4). If PRODUCT is used in a data-entry table, it should be used only in a header or footer cell. - Remainder
- Calculates the remainder after a value is divided by a divisor.
*Example*:`MOD(10, 3)`

returns 1. - Round
- Rounds a value to a specified number of decimal places.
*Example*:`ROUND(3.05882, 2)`

returns 3.06. - Square root
- Calculates the square root for a positive number. If the value is a
negative number,
Square
root
returns *.
*Example*: Control1 is a text box that contains the value 100.`SQRT(Control1)`

returns 10. - Sum
- Calculates the sum of a list of values.
*Example*: Control1 in a data-entry table contains the following values: 1, 2, 3, and 4.`SUM(Control1)`

returns 10. If you use SUM in a data-entry table, you must place the formula control in a header or footer cell. - Sum of product
- Calculates the sum of the product of two arrays of numbers.
*Example*: Control1 contains 2, 1, and 2. Control2 contains 2, 2, 3.`SUMPRODUCT(Control1, Control2)`

returns 12=(2*2 + 1*2 + 2*3). If SUMPRODUCT is used in a data-entry table, it should be used only in a header or footer cell.

- Count
- Calculates the number of observations.
*Example*: Control1 in a data-entry table contains the following values: 2, 3, 4, 7, 1.`Count(Control1)`

returns 5. - Inverse of standard normal cumulative distribution
- Calculates the value associated with the area under the probability
density function. The distribution has a mean of 0 and a standard deviation of
1.
*Example*:`NORMSINV(.05)`

returns -1.645, which is the point on the probability density curve that has 5% of the total area beneath it. - Maximum
- Returns the largest value in a series.
*Example*: Control1 in a data-entry table contains the following values: 2, 4, 6, 8, 10.`MAX(Control1)`

returns 10. If MAX is used in a data-entry table, it should be used only in a header or footer cell. - Mean
- Calculates the average or mean.
*Example*: Control1 in a data-entry table contains the following values: 2, 4, 6, 8, 10.`Mean(Control1)`

returns 6. If MEAN is used in a data-entry table, it should be used only in a header or footer cell. - Median
- Calculates the median or middle of the data. For a series with n
values, if n is odd, the median is the value in the middle. If n is even, the
median is the average of the two middle values.
*Example*: For example, Control1 in a data-entry table contains the following values: 1, 2, 3, 4, 5.`MEDIAN(Control1)`

returns 3. If MEDIAN is used in a data-entry table, it should be used only in a header or footer cell. - Minimum
- Returns the smallest value in a series.
*Example*: Control1 in a data-entry table contains the following values: 2, 4, 6, 8, 10.`MIN(Control1)`

returns 2. If MIN is used in a data-entry table, it should be used only in a header or footer cell. - Range
- Calculates the difference between the maximum value and the minimum
value in a series of values.
*Example*: If Control1 contains 10, 30, 40, and 60,`RANGE(Control1)`

returns 50=(60 - 10). If RANGE is used in a data-entry table, it should be used only in a header or footer cell. - Standard deviation
- Measures the dispersion (how spread out the data are) about the mean.
*Example*: Control1 in a data-entry table contains the following values: 80, 80, 80, and 80.`STDEV(Control1)`

returns 0 because the values do not vary. If the values are 80, 90, 100, and 110,`STDEV (Control1)`

returns 12.91. If STDEV is used in a data-entry table, it should be used only in a header or footer cell. - Standard normal cumulative distribution
- Returns the area under the probability density function. The
distribution has a mean of 0 and a standard deviation of 1.
*Example*:`NORMSDIST(0)`

returns 0.50 because 50% of the distribution falls below 0.

The syntax examples in this section use the following variables.
###### Note

- Num_periods is the number of payment periods.
- Rate is the constant interest rate each period.
- Type is when payments are due during a period, with 1 representing the beginning and 0 representing the end.

The following examples use US dollars.

- Payment (PMT)
- Calculates the periodic payment for an annuity with a constant interest
rate.
*Syntax*: PMT(rate, num_periods, present_value, future_value, type).*Example*: The payment for a $10,000, four-year loan at a rate of 7.25%, compounded monthly.`PMT(0.0725/12, 48, 10000, 0, 0)`

returns -$240.62; therefore, the payment would be $240.62 per month.

- Present value (PV)
- Calculates the present value of an investment as a result of payments
over a period of time.
*Syntax*: PV(rate, num_periods, payment, future_value, type).*Example*: Receiving payments of $100 once per year for the next five years at a 6.5% yearly interest rate.`PV(0.065, 5, 100, 0, 0)`

returns $415.57; therefore, receiving payments of $100 per year for five years would be the same as receiving $415.57 now.

- Future value (FV)
- Calculates the future value of an investment as a result of payments
over a period of time.
*Syntax*: FV(rate, num_periods, payment, present_value, type).*Example*: Starting with $0, investing $10 per month for a year at a 5% yearly interest rate, compounded monthly.`FV(0.05/12, 12, -10, 0, 0)`

returns $122.79; therefore, the investment would be worth $122.79 after a year.

###### Note

The rate, R, must be in decimal form and should specify the interest rate per period. For example, to calculate the future value of $5000 after 12 years with an annual interest rate of 6%, enter

`FV(.06, 12, 5000)`. To calculate the future value of $5000 after 12 quarters with an annual rate of 6%, enter`FV(.06/4, 12, 5000)`.

- And
- Checks whether all the conditions are true. Returns TRUE if all
conditions are met and FALSE otherwise. (Only used as the first term in an If
statement.)
*Example*:`IF(AND(Control1="yes", Control2="yes"), 1, 0)`

returns 1 if the value in Control1 and Control2 is "yes" and returns 0 for all other values. - Array if
- Returns a list of values which meet a condition. The data-entry table
with the formula must contain the same number of rows as the number of values
meeting the condition.
*Example*:`ARRAYIF(Control1, Control1>5)`

returns the list of values in Control 1 that are greater than 5.*Example*:`ARRAYIF(Control1, Control2=MAX(Control2))`

returns the value in Control1 associated with the maximum value in Control 2.

- Count if
- Returns the number of times when a condition is met. In a repeating row
or column, returns the number of times when a condition is met in the row or
column. In a header or footer, returns the number of times when a condition is
met in the table.
*Example*: Control1 contains the following values: 2,3,4,7, and 1.`COUNTIF(Control1>3)`

returns a 2. - If
- Checks whether a condition is met, and returns one value if true, and
another value if false.
*Example*:`IF(Control1="yes", 1, 0)`

returns 1 if the value in Control1 is "yes" and returns 0 for all other values. - Not
- Changes FALSE to TRUE, or TRUE to FALSE. Used to negate a logical
condition.
*Example*:`NOT(ISMISSING(Control1))`

returns 1 if Control1 contains a value and returns 0 if Control1 does not contain a value. - Or
- Checks whether any of the conditions are true. Returns TRUE if at least
one condition is met and FALSE otherwise. (Used only as the first term in an If
statement.)
*Example*:`IF(OR(Control1="yes", Control2="yes"), 1, 0)`

returns 1 if the value in Control1 or Control2 is "yes" and returns 0 if "yes" does not appear in either control.

Enclose text strings in double quotes.

- Concatenate
- Combines several text strings into one text string. The data type of
the formula control must be text.
*Example*: Control1 contains "Total" and control2 contains "Defects".`CONCATENATE(Control1, Control2)`

returns "TotalDefects ". - Find
- Returns the starting position of one text string within another text
string (counting from the left). FIND is case sensitive.
*Example*: Control1 contains "B32A64S".`FIND ("A", Control1)`

returns 4 because A is the 4th character in the text string.`FIND ("a", Control 1)`

returns an error message because the function is case-sensitive. - Left
- Returns the specified number of characters from the start of a text
string. The data type of the formula control must be text.
*Example*:`LEFT("Project Start Date", 7)`

returns "Project". - Length
- Calculates the number of characters (including blank spaces) in a text
string. The data type of the formula control must be text.
*Example*:`LENGTH("Project Start Date")`

returns 18. - Mid
- Returns the characters from the middle of a text string given a
starting position and number of characters. The data type of the formula
control must be text.
*Example*:`MID("Project Start Date", 9, 10)`

returns "Start Date". - Repeat
- Returns the character repeated a specified number of times. The
repeating character for the first argument must be enclosed in quotes (unless
it is a control). The data type of the formula control must be text.
*Example*: Control1 contains the values 2, 4, and 1 and is in integer format.`REPT("1",Control1)`

returns 11, 1111, and 1. - Right
- Returns the specified number of characters from the end of a text
string. The data type of the formula control must be text.
*Example*:`RIGHT("Project Start Date", 10)`

returns "Start Date". - Text
- Converts a numeric value to a text value. The data type of the formula
control must be text.
*Example*:`TEXT(10)`

returns "10".

- Date
- Returns the date from given year, month, and day.
*Example*:`DATE(2011, 10, 8)`

returns 10/8/2011 when the data type of the formula control is numeric, and the format is set to date. If the format is not date, desktop app returns the number that represents the date in a numerical date-time code. - Date difference
- Returns the number of elapsed days from one date to the next (the
difference between the two dates). The data type of the formula control must be
numeric.
*Example*:`DATEDIF(Control1, Control2)`

returns 12 when control1 contains the date 10/13/2011 and control2 contains the date 10/25/2011. - Day
- Returns the day of the month, a number from 1 to 31.
*Example*: The date picker control1 contains the date December 20, 2021.`DAY(Control1)`

returns 20. - Month
- Returns the month, a number from 1 (January) to 12 (December).
*Example*: The date picker control1 contains the date December 20, 2021.`MONTH(Control1)`

returns 12. - Year
- Returns the year of a date.
*Example*: The date picker Control1 contains the date December 20, 2021.`YEAR(Control1)`

returns 2021.

- Array
- Returns a list of values from a data table that meet a condition.
`ARRAY`

requires at least two arguments.*Example*:`ARRAY(1, 2, 3, 4)`

in Control1 of a data-entry table with 4 rows returns the following results.- 1
- 2
- 3
- 4

- Error
- Generates an error with a tool tip message. In the formula, enclose the
tool tip text in quotes. Use as part of an IF statement.
*Example*: You create a form that requires a "yes" answer to proceed.`IF(Control1="yes", "Continue", ERROR("This value must be yes to complete the remainder of this form."))`

returns "Continue" if the value in Control1 is "yes" and returns the error message if Control1 is not "yes". - Index
- Returns an index of rows or columns in increments of 1, beginning with
the first row or column. This function does not use an argument (do not insert
a value between the parentheses). The formula control for this function must be
in a data-entry table.
*Example*: If a data-entry table has 5 rows,`INDEX( )`

returns the following results.- 1
- 2
- 3
- 4
- 5

- Is missing
- Checks if a value is missing or if a formula generates an error.
Returns 1 (true) or 0 (false).
*Example*: Control1 contains a Numeric Box control.`ISMISSING(Control1)`

returns 1 if the Numeric Box does not contain a value and 0 if the Numeric Box does contain a value. - Sequence
- Returns a sequence of numbers based on the starting number and the
increment you specify. The formula control for this function must be in a
data-entry table.
*Example*: If a data-entry table has 5 rows,`SEQUENCE(-10, 4)`

returns the following results.- -10
- -6
- -2
- 2
- 6