Microsoft Office Tutorials and References

In Depth Information

**Chapter 4: Introducing Worksheet Functions**

Simplify your formulas

Using a built-in function can simplify a formula significantly. For example, you might need to

calculate the average of the values in 10 cells (A1:A10). Without the help of any functions, you would

need to construct a formula like this:

=(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10

Not very pretty, is it? Even worse, you would need to edit this formula if you inserted a new row

in the A1:A10 range and needed the new value to be included in the average. However, you can

replace this formula with a much simpler one that uses the AVERAGE function:

=AVERAGE(A1:A10)

Perform otherwise impossible calculations

Functions permit formulas to perform calculations that go beyond the standard mathematical

operations. Perhaps you need to determine the largest value in a range. A formula can’t tell you

the answer without using a function. This formula uses the MAX function to return the largest

value in the range A1:D100:

=MAX(A1:D100)

Speed up editing tasks

Functions can sometimes eliminate manual editing. Assume that you have a worksheet that

contains 1,000 names in cells A1:A1000 and that all the names appear in all-uppercase letters. Your

boss sees the listing and informs you that you need to mail-merge the names with a form letter

and that the use of all uppercase is not acceptable. For example, JOHN F. CRANE must appear as

John F. Crane. You
could
spend the rest of the afternoon reentering the list — or you could use a

formula such as the following, which uses the PROPER function to convert the text in cell A1 to

proper case:

=PROPER(A1)

1.
Type this formula in cell B1 and then copy it down to the next 999 rows.

2.
Select B1:B1000 and choose Home

➜

Clipboard

➜

Copy to copy the range to the Clipboard

(or press Ctrl+C).