Microsoft Office Tutorials and References

In Depth Information

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

haps 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 ac-

ceptable. For example, JOHN F. CRANE must appear as John F. Crane. You
could
spend the rest of the after-

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

3.
Activate cell A1 and choose Home
⇒
Clipboard
⇒
Paste
⇒
Paste Values to convert the formulas to values.

4.
Delete column B.

You're finished! With the help of a function, you just eliminated several hours of tedious work in less than a

minute.

Excel 2013 introduces a new feature — Flash Fill — that can sometimes take the place

of formulas for text-conversion tasks like the one described here. See Chapter 16 for

details.

Provide decision-making capability

You can use the Excel IF function to give your formulas decision-making capabilities. Suppose that you have a

worksheet that calculates sales commissions. If a salesperson sells at least $100,000 of product, the commission

rate reaches 7.5 percent; otherwise, the commission rate remains at 5.0 percent. Without using a function, you

would need to create two different formulas and make sure that you use the correct formula for each sales