Microsoft Office Tutorials and References

In Depth Information

**Using functions in your formulas**

Not very pretty, is it? Even worse, you would need to edit this formula if you added another

cell to the range. Fortunately, you can replace this formula with a much simpler one that

uses one of Excel’s built-in worksheet functions,
AVERAGE
:

=AVERAGE(A1:A10)

The following formula demonstrates how using a function can enable you to perform

calculations that are not otherwise possible. Say you need to determine the largest value in a

range. A formula can’t tell you the answer without using a function. Here’s a formula that

uses the
MAX
function to return the largest value in the range A1:D100:

=MAX(A1:D100)

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

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

Your boss sees the listing and informs you that the names will be mail-merged with a form

letter. All-uppercase letters is not acceptable; for example,
JOHN F. SMITH
must now

appear as
John F. Smith
. You
could
spend the next several hours re-entering the list

(ugh), or you could use a formula, such as the following, which uses the
PROPER
function

to convert the text in cell A1 to the proper case:

=PROPER(A1)

Enter this formula once in cell B1 and then copy it down to the next 999 rows. Then select

B1:B1000 and choose Home

Copy to copy the range. Next, with B1:B1000 still

selected, choose Home
➪
Clipboard
➪
Paste arrow
➪
Paste Values (V) to convert the formulas

to values. Delete the original column, and you’ve just accomplished several hours of work in

less than a minute.

➪

Clipboard

➪

One last example should convince you of the power of functions. Suppose you have a

worksheet that calculates sales commissions. If the salesperson sold more than $100,000

of product, the commission rate is 7.5 percent; otherwise, the commission rate is 5.0

percent. Without using a function, you would have to create two different formulas and make

sure that you use the correct formula for each sales amount. A better solution is to write

a formula that uses the
IF
function to ensure that you calculate the correct commission,

regardless of sales amount:

=IF(A1<100000,A1*5%,A1*7.5%)

This formula performs some simple decision making. The formula checks the value of cell

A1. If this value is less than 100,000, the formula returns cell A1 multiplied by 5

percent. Otherwise, it returns what’s in cell A1 multiplied by 7.5 percent. This example uses

three arguments, separated by commas. I discuss this in the upcoming section, “Function

arguments.”