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 contents of 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().

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, as a formula. For example, the function =SUM(A1:A5,C2:C10,D3:D17) has only three arguments but 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 can, in turn, 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.

Types of Arguments

In the examples presented so far, all the arguments have been cell or range references. You can also use numbers, text, logical values, range names, arrays, and error values as arguments.

Numeric Values

The arguments to a function can be numeric. For example, the SUM function in the formula =SUM(327,209,176) adds the numbers 327, 209, and 176. Usually, however, you type the numbers you want to use in cells of a worksheet and then use references to those cells as arguments to your functions.

Text Values

You can also use text as an argument to a function. For example, in the formula =TEXT(NOW( ),"mmm d, yyyy^") the second argument to the TEXT function, mmm d, yyyy, is a text argument specifically recognized by Excel. It specifies a pattern for converting the serial date value returned by NOW into a text string. Text arguments can be text strings enclosed in quotation marks or references to cells that contain text.

For more about text functions, see “Understanding Text Functions” on page 466.

Logical Values

The arguments to a few functions specify only that an option is either set or not set; you can use the logical values TRUE to set an option and FALSE to specify that the option isn’t set. A logical expression returns the values TRUE or FALSE (which evaluate to 1 and 0, respectively) to the worksheet or the formula containing the expression. For example, the first argument of the IF function in the formula =IF(A1=TRUE,"Future", "Past ")&"History" is a logical expression that uses the value in cell A1. If the value in A1 is TRUE (or 1), the expression A1=TRUE evaluates to TRUE, the IF function returns Future, and the formula returns the text Future History to the worksheet.

For more about logical functions, see “Understanding Logical Functions” on page 471.

Named References

You can use a defined name as an argument to a function. For example, if you click the Formulas tab on the Ribbon and use the Define Name button to assign the name Qtrly-Income to the range C3:C6, you can use the formula =SUM(QtrlyIncome) to total the numbers in cells C3, C4, C5, and C6.

For more about names, see “Naming Cells and Cell Ranges” on page 407.

Arrays

You can use an array as an argument in a function. Some functions, such as TREND and TRANSPOSE, require array arguments; other functions don’t require array arguments but do accept them. Arrays can consist of numbers, text, or logical values.

For more about arrays, see “Using Arrays” on page 434.

Mixed Argument Types

You can mix argument types within a function. For example, the formula =AVERAGE(Groupl,A3,5*3) uses a defined name (Group1), a cell reference (A3), and a numeric expression (5*3) to arrive at a single value. All three are acceptable.