Microsoft Office Tutorials and References

In Depth Information

**Exploring the syntax of functions**

Exploring the syntax of functions

Worksheet functions have two parts: the name of the function and the arguments that

follow. Function names—such as SUM and AVERAGE—describe the operation the function

performs. Arguments specify the values or cells to be used by the function. For example,

the function ROUND has the following syntax: =ROUND(
number
,
num_digits
), as in the

formula =ROUND(M30,2). The M30 part is a cell reference entered as the
number
argument—

the value to be rounded. The 2 part is the
num_digits
argument. The result of this function

is a number (whatever the value in cell M30 happens to be) rounded to two decimal places.

Parentheses surround function arguments. The opening parenthesis must appear

immediately after the name of the function. If you add a space or some other character between

the name and the opening parenthesis, the error value #NAME? appears in the cell.

Note

A few functions—such as PI, TRUE, and NOW—have no arguments. (You usually nest

these functions in other formulas.) Even though they have no arguments, you must

place an empty set of parentheses after them, as in =NOW( ), so that Excel knows they

are functions and not defined names.

When you use more than one argument in a function, you separate the arguments with

commas. For example, the formula =PRODUCT(C1,C2,C5) tells Excel to multiply the

numbers in cells C1, C2, and C5. Some functions, such as PRODUCT and SUM, take an

unspecified number of arguments. You can use as many as 255 arguments in a function, as long

as the total length of the formula does not exceed 8,192 characters. However, you can use

a single argument or a range that refers to any number of cells in your worksheet in a

formula. For example, the function =SUM(A1:A5,C2:C10,D3:D17) has only three arguments,

but it actually totals the values in 29 cells. (The first argument, A1:A5, refers to the range of

five cells from A1 through A5, and so on.) The referenced cells, in turn, can also contain

formulas that refer to more cells or ranges.

Expressions as arguments

You can use combinations of functions to create an expression that Excel evaluates

to a single value and then interprets as an argument. For example, in the formula

=SUM(SIN(A1
*
PI( )),2
*
COS(A2
*
PI( ))) the comma separates two complex expressions that

Excel evaluates and uses as the arguments of the SUM function.