Microsoft Office Tutorials and References

In Depth Information

**Function Argument Types**

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 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.)

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)

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 to include in the sum.

You may 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

does not use cells beyond them when computing a formula result that references an entire

column or row.