More Power with @functions

@Functions are built-in formulas that perform specialized calculations. You can use an @function by itself, or combine it with other @functions and formulas.

Some @functions perform simple calculations; for example, @SUM(D2..D7) adds the values in the range D2..D7. Using this @function is easier than entering your own formula to sum all the values in this range: +D2+D3+D4+D5+D6+D7.

Many @functions further simplify your work by performing complex calculations. For example, @NPV calculates the net present value of a series of future cash-flow values.



Parts of an @function

@Functions can contain these parts: an @ (at symbol), the name of the function, one or more arguments, parentheses, and argument separators.

Parts of an @function



Arguments

Many @functions require you to supply data to work on. This data is called an argument. For example, in the following @function, the argument is the range address D1..D7, enclosed in parentheses:


@SUM(D1..D7)

Depending on the particular @function, an argument can be a single value, a range of cells, text, or another @function.

Arguments can be required or optional. You must enter required arguments, but you can omit the optional ones. Optional arguments are enclosed in [ ] (brackets) in Help descriptions of individual @functions.

If an @function contains more than one optional argument, you must use the optional arguments sequentially. You can't use an optional argument without using the optional argument(s) that precede it. You can, however, use an optional argument without using subsequent optional arguments.



Argument separators

When you use more than one argument with an @function, you separate the arguments with an argument separator character, typically a , (comma) or a ; (semicolon).



Parentheses

Parentheses enclose @function arguments. Nested parentheses enclose an @function that you use as an argument for another @function. For example, you use nested parentheses when you use @SUM and its argument as the argument indicating the amount of the principal of a loan for @PMT as follows:


@PMT(@SUM(D1..D7),0.105/12,48)


Quotation marks

In @functions that accept text arguments, quotation marks enclose the text for the text arguments. Text not enclosed in quotation marks is assumed to be a range name.



Argument types

There are four types of @function arguments: condition, location, text, and value. Some @functions, such as @TODAY, do not use arguments.



Condition

A condition is an expression that uses a logical operator (=, < , >, <>, >=, <=, #NOT#, #AND#, and #OR#), or the address or name of a cell containing a logical operator. You can also use a formula, @function, or a number (1 for true, 0 for false) as a condition. A conditional @function, such as @IF, evaluates the condition argument and proceeds according to whether it is true or false.



Location

A location argument is the address or name of a range; or a formula or @function that produces the address or name of a range. A location argument can refer to a single cell or a multiple-cell range.



Value

A value is a number, the address or name of a cell that contains a number, or a formula or @function that produces a number.



Text

Some @functions, such as @INFO, use text arguments. A text argument is any sequence of letters, numbers, and symbols enclosed in " " (quotation marks), the address or name of a cell that contains text, or a formula or @function that produces text.

You mark a cell entry as text by preceding the entry with a text-prefix character. The default text-prefix character (an apostrophe) is autiomatically inserted at the beginning of any cell entry that starts with a letter. When you enter numbers or symbols in a cell, in order for them to be considered as text, you must precede them with a text-prefix character. For more information, see entering text.



See also
More power with formulas
Entering an @function
List of @functions A-Z