Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Introducing Worksheet Functions
3. Activate cell A1 and choose Home
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:
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
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.