Microsoft Office Tutorials and References

In Depth Information

**Chapter 4: Introducing Worksheet Functions**

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.

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 amount. This formula uses the

IF function to check the value in cell A1 and make the appropriate commission calculation:

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

The IF function takes three arguments, each separated by a comma. These arguments provide

input to the function. The formula is making a decision: If the value in cell A1 is less than 100,000,

then return the value in cell A1 multiplied by 5 percent. Otherwise, return the value in cell A1

multiplied by 7.5 percent.

More about functions

All told, Excel includes more than 400 functions. And if that’s not enough, you can purchase

additional specialized functions from third-party suppliers, and you can even create your own

custom functions (using VBA).

If you’re ready to create your own custom functions by using VBA, check out Part VI of

this topic.

The sheer number of available worksheet functions may overwhelm you, but you’ll probably find

that you use only a dozen or so of the functions on a regular basis. And as you’ll see, the Function

Library group on the Formulas tab (described later in this chapter) makes it easy to locate and

insert a function, even if you use it only rarely.

Appendix A contains a complete listing of Excel’s worksheet functions, with a brief

description of each.