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