Microsoft Office Tutorials and References

In Depth Information

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

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

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

And, make sure your formula isn't in the column that's being referenced. If the SUM formula above is in

column B, it will generate a circular reference error.

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.

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 may want to make it perfectly

clear that the value in the cell is 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 three letters (Bud):