Export (0) Print
Expand All
ABS
AND
ASC
COS
DAY
DDB
EXP
FV
IF
INT
LEN
LN
LOG
MAX
Me
MID
MIN
MOD
NOT
IS
NPV
ODD
OR
PI
PMT
PV
SIN
SLN
SUM
SYD
T
TAN
VAR
Expand Minimize

About functions

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function rounds off a number in the Cost column.

=ROUND(Cost, 2)

Structure The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.

Function name Name of a function that is supported by lists. Each function takes a specific number of arguments, processes them, and returns a value.

Arguments Arguments can be numbers, text, logical values such as True or False, or column references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

Argument tooltip A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.

Nested functions

In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the sum of two column values.

=AVERAGE([Cost1], SUM([Cost2]+[Discount]))

Valid returns When a function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument uses Yes or No, then the nested function must return Yes or No. If it doesn't, the list displays a #VALUE! error value.

Nesting level limits A formula can contain up to eight levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the SUM function is a second-level function because it is an argument of the AVERAGE function. A function nested within the SUM function would be a third-level function, and so on.

Note: Lists do not support the RAND and NOW functions. The TODAY and ME functions are not supported in calculated columns, but are supported in the default value setting of a column.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft