Function Argument Types

If you examine the preceding examples in this chapter, you'll notice that all the functions use a set of parentheses. The information within the parentheses is the function's arguments. Functions vary in how they use arguments. A function may use

• No arguments

• One argument

• A fixed number of arguments

• An indeterminate number of arguments

• Optional arguments

For example, the RAND function, which returns a random number between 0 and 1, doesn't use an argument. Even if a function doesn't require an argument, you must provide a set of empty parentheses, like this:

 =RAND() 

If a function uses more than one argument, a comma separates the arguments. For example, the LARGE function, which returns the nth largest value in a range, uses two arguments. The first argument represents the range; the second argument represents the value for n. The formula below returns the third-largest value in the range A1:A100:

 =LARGE(A1:A100,3) 

Note 

The character used to separate function arguments can be something other than a comma-for example, a semicolon. This character is determined by the List Separator setting for your system, which is specified in the Regional Settings dialog box, accessible via the Control Panel.

The examples at the beginning of the chapter used cell or range references for arguments. Excel proves quite flexible when it comes to function arguments, however. The following sections demonstrate additional argument types for functions.

Accommodating Former Lotus 1-2-3 Users

If you've ever used any of the 1-2-3 spreadsheets (or any version of Corel's Quattro Pro), you might recall that these products require you to type an "at" sign (@) before a function name. Excel is smart enough to distinguish functions without you having to flag them with a symbol.

Because old habits die hard, however, Excel accepts @ symbols when you type functions in your formulas, but it removes them as soon as you enter the formula.

These competing products also use two dots (..) as a range reference operator-for example, A1..A10. Excel also enables you to use this notation when you type formulas, but Excel replaces the notation with its own range reference operator, a colon (:).

This accommodation goes only so far, however. Excel still insists that you use the standard Excel function names, and it doesn't recognize or translate the function names used in other spreadsheets. For example, if you enter the 1-2-3 @AVG function, Excel flags it as an error. (Excel's name for this function is AVERAGE.)

Names as Arguments

As you've seen, functions can use cell or range references for their arguments. When Excel calculates the formula, it simply uses the current contents of the cell or range to perform its calculations. The SUM function returns the sum of its argument(s). To calculate the sum of the values in A1:A20, you can use

 =SUM(A1:A20) 

And, not surprisingly, if you've defined a name for A1:A20 (such as Sales), you can use the name in place of the reference:

 =SUM(Sales) 

Cross Ref 

For more information about defining and using names, refer to Chapter 3.

Full-Column or Full-Row as Arguments

In some cases, you may find it useful to use an entire column or row as an argument. For example, the following formula sums all values in column B:

 =SUM(B:B) 

Using full-column and full-row references is particularly useful if the range that you're summing changes-if you continually add new sales figures, for instance. If you do use an entire row or column, just make sure that the row or column doesn't contain extraneous information that you don't want included in the sum.

You might think that using such a large range (a column consists of 1,048,576 cells) might slow down calculation time. Not true. Excel keeps track of the last-used row and last-used column and will not use cells beyond them when computing a formula result that references an entire column or row.

Literal Values as Arguments

A literal argument refers to a value or text string that you enter directly. For example, the SQRT function, which calculates the square root of a number, takes one argument. In the following example, the formula uses a literal value for the function's argument:

 =SQRT(225) 

Using a literal argument with a simple function like this one usually defeats the purpose of using a formula. This formula always returns the same value, so you could just as easily replace it with the value 15. You may want to make an exception to this rule in the interest of clarity. For example, you might want to make it perfectly clear that you are computing the square root of 225.

Using literal arguments makes more sense with formulas that use more than one argument. For example, the LEFT function (which takes two arguments) returns characters from the beginning of its first argument; the second argument specifies the number of characters. If cell A1 contains the text Budget, the following formula returns the first letter, or B:

 =LEFT(A1,1) 

Expressions as Arguments

Excel also enables you to use expressions as arguments. Think of an expression as a formula within a formula. When Excel encounters an expression as a function's argument, it evaluates the expression and then uses the result as the argument's value. Here's an example:

 =SQRT((A1^2)+(A2^2)) 

This formula uses the SQRT function, and its single argument appears as the following expression:

 (A1^2)+(A2^2) 

When Excel evaluates the formula, it first evaluates the expression in the argument and then computes the square root of the result.

Other Functions as Arguments

Because Excel can evaluate expressions as arguments, it shouldn't surprise you that these expressions can include other functions. Writing formulas that have functions within functions is sometimes known as nesting functions. Excel starts by evaluating the most deeply nested expression and works its way out. Note this example of a nested function:

 =SIN(RADIANS(B9)) 

The RADIANS function converts degrees to radians, the unit used by all of the Excel trigonometric functions. If cell B9 contains an angle in degrees, the RADIANS function converts it to radians and then the SIN function computes the sine of the angle.

New 

A formula can contain up to 64 levels of nested functions, up from 7 in previous versions. If you exceed this level, Excel pops up an error message. In the vast majority of cases, this limit poses no problem. In the unlikely event that you need to exceed this limit, you may be able to use a lookup function instead. See Chapter 8 for more information about lookup functions.

Arrays as Arguments

A function can also use an array as an argument. An array is a series of values separated by a comma and enclosed in brackets. The formula below uses the OR function with an array as an argument. The formula returns TRUE if cell A1 contains 1, 3, or 5.

 =OR(A1={1,3,5}) 

Cross Ref 

See Part IV of this book for more information about working with arrays.

Often, using arrays can help you simplify your formula. The formula below, for example, returns the same result but uses nested IF functions instead of an array:

 =IF(A1=1,TRUE,IF(A1=3,TRUE,IF(A1=5,TRUE,FALSE)))