A thorough knowledge of Excel's worksheet functions is essential for anyone who wants to master the art of formulas. This chapter provides an overview of the functions available for use in formulas.
A worksheet function is a built-in tool that you use in a formula. A typical function (such as SUM) takes one or more arguments and then returns a result. The SUM function, for example, accepts a range argument and then returns the sum of the values in that range.
You'll find functions useful because they
- Simplify your formulas
- Permit formulas to perform otherwise impossible calculations
- Speed up some editing tasks
- Allow conditional execution of formulas-giving them rudimentary decision-making capability
The examples in the sections that follow demonstrate each of these points.
Using a built-in function can simplify a formula significantly. For example, you might need to calculate the average of the values in 10 cells (A1:A10). Without the help of any functions, you would need to construct a formula like this:
=(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10
Not very pretty, is it? Even worse, you would need to edit this formula if you expanded the range to be summed. You can replace this formula with a much simpler one that uses one of Excel's built-in worksheet functions. For example, the following formula uses the AVERAGE function:
=AVERAGE(A1:A10)
Functions permit formulas to perform impossible calculations. Perhaps you need to determine the largest value in a range. A formula can't tell you the answer without using a function. This simple formula uses the MAX function to return the largest value in the range A1:D100:
=MAX(A1:D100)
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 acceptable. For example, JOHN F. CRANE must appear as John F. Crane. You could spend the rest of the day 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)
- Enter this formula in cell B1 and then copy it down to the next 999 rows.
- Select B1:B1000 and use the Home
- Activate cell A1 and use the Home
- Delete column B.
You're finished! With the help of a function, you just accomplished several hours of work in less than a minute.
Functions can also give your formulas decision-making capability. Suppose you have a worksheet that calculates sales commissions. If a salesperson sells more than $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. Note this formula that 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%)
This formula uses the IF function, which 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.
All told, Excel includes more than 300 functions. And if that's not enough, you can purchase additional specialized functions from third-party suppliers, and even create your own custom functions (using VBA).
Cross Ref | If you're ready to create your own custom functions by using VBA, check out Part IV of this book. |
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 Excel Function Library Group on the Formula tab (described later in this chapter) makes it easy to locate and insert a function, even if you use it only rarely