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