Microsoft Office Tutorials and References

In Depth Information

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 when you use the

function in a formula, like this:

=RAND()

If a function uses more than one argument, a comma separates the arguments. For example, the LARGE func-

tion, which returns the
n
th 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)

In some non-English versions of Excel, the character used to separate function argu-

ments can be something other than a comma β for example, a semicolon. The ex-

amples in this book use a comma as the argument separator character.

The examples at the beginning of the chapter use cell or range references for arguments. Excel proves quite

flexible when it comes to function arguments, however. The following sections demonstrate additional argu-

ment types for functions.

Accommodating former Lotus 1-2-3 users

If you've ever used any of the Lotus 1-2-3 spreadsheets (or any version of Corel's Quattro Pro), you may recall

that these products require you to type an βatβ sign (@) before a function name. Excel is smart enough to distin-

guish 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 other spreadsheet programs also use two dots (..) as a range reference operator β for example, A1..A10.

Excel also allows you to use this notation when you type formulas, but Excel replaces the dots with its own range

reference operator, a colon (:). In fact, you can use any number of dots as a range reference operator, even

something like this: A1...........A10.

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 for-

mula, it 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