In This Chapter
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. Worksheet functions allow you to perform calculations or operations that would otherwise be impossible. 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
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 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:
=AVERAGE(A1:A10)
Functions permit formulas to perform calculations that go beyond the standard mathematical operations. Perhaps 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:
=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 afternoon 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)
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.
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.
All told, Excel 2016 includes more than 400 functions. And if that’s not enough, you can purchase additional specialized functions from third-party suppliers. You can even create your own custom functions using VBA.
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.
If you examine the preceding examples in this chapter, you’ll notice that all the functions use a set of parentheses. The information within the parentheses is the function’s arguments. Functions vary in the way they use arguments. A function may use
For example, the RAND function, which returns a random number between 0 and 1, doesn’t use an argument. Even if a function doesn’t require an argument, you must provide a set of empty parentheses when you use the function in a formula, like this:
=RAND()
If a function uses more than one argument, a comma separates the arguments. For example, the LARGE function, which returns the nth largest value in a range, uses two arguments. The first argument represents the range; the second argument represents the value for n. The formula that follows returns the third-largest value in the range A1:A100:
=LARGE(A1:A100,3)
The examples at the beginning of the chapter use cell or range references for arguments. Excel proves quite flexible when it comes to function arguments, however. The following sections demonstrate additional argument types for functions.
As you’ve seen, functions can use cell or range references for their arguments. When Excel calculates the formula, it uses the current contents of the cell or range to perform its calculations. The SUM function returns the sum of its argument(s). To calculate the sum of the values in A1:A20, you can use this:
=SUM(A1:A20)
And, not surprisingly, if you’ve defined a name for A1:A20 (such as Sales), you can use the name in place of the reference:
=SUM(Sales)
In some cases, you may find it useful to use an entire column or row as an argument. For example, the following formula sums all values in column B:
=SUM(B:B)
Using full-column and full-row references is particularly useful if the range that you’re summing changes—if you continually add new sales figures, for instance. If you do use an entire row or column, just make sure that the row or column doesn’t contain extraneous information that you don’t want to include in the sum.
And, make sure your formula isn’t in the column that’s being referenced. If the preceding SUM formula is in column B, it will generate a circular reference error.
You may think that using such a large range (a column consists of 1,048,576 cells) might slow down calculation time. Not true. Excel keeps track of the last-used row and last-used column and does not use cells beyond them when computing a formula result that references an entire column or row.
A literal argument refers to a value or text string that you enter directly. For example, the SQRT function, which calculates the square root of a number, takes one argument. In the following example, the formula uses a literal value for the function’s argument:
=SQRT(225)
Using a literal argument with a simple function like this one usually defeats the purpose of using a formula. This formula always returns the same value, so you could just as easily replace it with the value 15. You may want to make an exception to this rule in the interest of clarity. For example, you may want to make it perfectly clear that the value in the cell is the square root of 225.
Using literal arguments makes more sense with formulas that use more than one argument. For example, the LEFT function (which takes two arguments) returns characters from the beginning of its first argument; the second argument specifies the number of characters. If cell A1 contains the text Budget, the following formula returns the first three letters (Bud):
=LEFT(A1,3)
You can also use expressions as arguments. Think of an expression as a formula within a formula (but without the leading equal sign). When Excel encounters an expression as a function’s argument, it evaluates the expression and then uses the result as the argument’s value. Here’s an example:
=SQRT((A1^2)+(A2^2))
This formula uses the SQRT function, and its single argument consists of the following expression:
(A1^2)+(A2^2)
When Excel evaluates the formula, it first evaluates the expression in the argument and then computes the square root of the result. This expression squares the value in cell A1 and adds it to the square of the value in cell A2.
Because Excel can evaluate expressions as arguments, it shouldn’t surprise you that these expressions can include other functions. Writing formulas that have functions within functions is sometimes known as nesting functions. Excel starts by evaluating the most deeply nested expression and works its way out.
Here’s an example of a nested function:
=SIN(RADIANS(B9))
The RADIANS function converts degrees to radians, the unit that all Excel trigonometric functions use. If cell B9 contains an angle in degrees, the RADIANS function converts it to radians, and then the SIN function computes the sine of the angle.
A formula can contain up to 64 levels of nested functions—a limit that will probably never be a factor.
A function can also use an array as an argument. An array is a series of values separated by a comma and enclosed in curly brackets. The formula that follows uses the OR function with an array as an argument. The formula returns TRUE if cell A1 contains 1, 3, or 5.
=OR(A1={1,3,5})
Often, using arrays can help simplify your formula. The following formula, for example, returns the same result as the previous formula but uses nested IF functions instead of an array:
=IF(A1=1,TRUE,IF(A1=3,TRUE,IF(A1=5,TRUE,FALSE)))
You can enter a function into a formula by typing it manually, by using the Function Library commands, or by using the Insert Function dialog box.
If you’re familiar with a particular function—that is, you know its correct spelling and the types of arguments that it takes—you may choose to simply type the function and its arguments into your formula. Often, this method is the most efficient.
However, you can also use the handy Formula AutoComplete feature. When you type an equal sign and the first letter of a function in a cell, Excel displays a drop-down list box of all the functions that begin with that letter and a ScreenTip with a brief description for the function (see Figure 4.1). You can continue typing the function to limit the list or use the arrow keys to select the function from the list. After you select the desired function, press Tab to insert the function and its opening parenthesis into the formula.
After you press Tab to insert the function and its opening parenthesis, Excel displays another ScreenTip that shows the arguments for the function (see Figure 4.2). The bold argument is the argument that you are entering. Arguments shown in square brackets are optional. Notice that the text in the ScreenTip contains a hyperlink for each argument that you’ve entered. Click a hyperlink to select the corresponding argument. If that ScreenTip gets in your way, you can drag it to a different location.
If you don’t like using Formula AutoComplete, you can disable this feature. Choose File ➜ Options to display the Excel Options dialog box. On the Formulas tab, remove the check mark from the Formula AutoComplete option.
Another way to insert a function into a formula is to use the icons in the Formulas ➜ Function Library group. Figure 4.3 shows these icons, each of which is a drop-down control.
Each Function category is dedicated to a specific topic area.
When you select a function from one of these lists, Excel displays its Function Arguments dialog box to help you enter the arguments. See the section, “Using the Insert Function dialog box” for more information about the Function Arguments dialog box.
The Insert Function dialog box is another way to enter a function into a formula. Using the Insert Function dialog box ensures that you spell the function correctly and that it contains the proper number of arguments in the correct order.
To insert a function, select the function from the Insert Function dialog box, as shown in Figure 4.4. You access this dialog box in several ways:
The Insert Function dialog box contains a drop-down list of categories. When you select a category from the list, the list box displays the functions in the selected category. The Most Recently Used category lists the functions that you’ve used most recently. The All category lists all the functions available across all categories. Access this category if you know a function’s name but not its category.
If you’re not sure which function to use, you can search for a function. Use the field at the top of the Insert Function dialog box. Type one or more keywords and click Go. Excel then displays a list of functions that match your search criteria. For example, if you’re looking for functions to calculate a loan payment, type loan as the search term.
When you select a function from the Select a Function list box, notice that Excel displays the function (and its argument names) in the dialog box, along with a brief description of what the function does. Also, you can click Help on This Function to read about the selected function in Excel’s Help system.
When you locate the function that you want to use, click OK. Excel’s Function Arguments dialog box appears, as shown in Figure 4.5, where you can specify the arguments for the function. To specify a cell or range as an argument, just click in the worksheet and point to the cell or range. Note that each argument is described.
When you choose Formulas ➜ Function Library ➜ AutoSum (or Home ➜ Editing ➜ AutoSum), Excel does a quick check of the surrounding cells. It then proposes a formula that uses the SUM function. If Excel guessed your intentions correctly, just press Enter to accept the proposed formula(s). If Excel guessed incorrectly, you can simply select the range with your mouse to override Excel’s suggestion (or press Esc to cancel the AutoSum).
You can preselect the cells to be included in an AutoSum rather than let Excel guess which cells you want. To insert a SUM function into cell A11 that sums A1:A10, select A1:A11 and then click the AutoSum button.
The AutoSum button displays an arrow that, when clicked, displays additional functions. For example, you can use this button to insert a formula that uses the AVERAGE function.
When you’re working with a table (created by using Insert ➜ Tables ➜ Table), you can choose Table Tools ➜ Design ➜ Total Row, and Excel displays a new row at the bottom of the table that contains summary formulas for the columns. See Chapter 9 for more information about tables.
When you choose Data ➜ Data Tools ➜ Outline ➜ Subtotal, Excel displays a dialog box that enables you to specify some options. Then it proceeds to insert rows and enter some formulas automatically. These formulas use the SUBTOTAL function.
The following list contains some additional tips to keep in mind when you use the Insert Function dialog box to enter functions:
18.224.69.83