Chapter 4

Understanding functions

The formulas that you can construct based on the information presented in Part I, “Mastering Excel formulas,” can range from simple additions and subtractions to powerful iteration-based solutions for otherwise difficult problems. Formulas that combine Excel’s operators with basic operands such as numeric and string values are the bread and butter of any spreadsheet.

But to get to the real meat of a spreadsheet model, you need to expand your formula repertoire to include Excel’s worksheet functions. Dozens of these functions exist, and they’re essential to making your worksheet easier to work with and more powerful. Excel has various function categories, including the following:

• Database and table

• Date and time

• Financial

• Information

• Logical

• Lookup and reference

• Math and trigonometry

• Statistical

• Text

This chapter gives you a short introduction to Excel’s built-in worksheet functions. You’ll find out what the functions are, what they can do, and how to use them. The next five chapters give you detailed descriptions of the functions in the preceding list of categories. (The exceptions are the statistical category, which I cover in Chapter 11, “Building descriptive statistical formulas,” the financial category, which I cover in Chapters 14, 15, and 16, and the database and table category, which I cover in Chapter 17, “Analyzing data with tables.” )

• Functions make simple but cumbersome formulas easier to use. For example, suppose that you want to add a list of 100 numbers in a column, starting at cell A1 and finishing at cell A100. It’s unlikely that you have the time or patience to enter 100 separate additions in a cell (that is, the formula =A1+A2+...+A100). Luckily, there’s an alternative: the SUM() function. With this function, you’d just enter =SUM(A1:A100).

• Functions enable you to include in your worksheets complex mathematical expressions that otherwise would be difficult or impossible to construct using simple arithmetic operators. For example, determining a mortgage payment given the principal, interest, and term is a complicated matter at best, but you can do it with Excel’s PMT() function just by entering a few arguments.

• Functions enable you to include data in your applications that you couldn’t access otherwise. For example, the INFO() function can tell you how much memory is available on your system, what operating system you’re using, what version number it is, and more. Similarly, the powerful IF() function enables you to test the contents of a cell—for example, to see whether it contains a particular value or an error—and then perform an action accordingly, depending on the result.

As you can see, functions are a powerful addition to your worksheet-building arsenal. With proper use of these tools, there is no practical limit to the kinds of models you can create.

Click here to view code image

FUNCTION(argument1, argument2, ...)

The FUNCTION part is the name of the function, which always appears in uppercase letters (such as SUM or PMT). Note, however, that you don’t need to type the function name using uppercase letters. Whatever case you use, Excel automatically converts the name to all uppercase. In fact, it’s good practice to enter function names using only lowercase letters. That way, if Excel doesn’t convert the function name to uppercase, you know that it doesn’t recognize the name, which means you probably misspelled it.

The items that appear within the parentheses and are separated by commas are the function arguments. The arguments are the function’s inputs—the data the function uses to perform its calculations. With respect to arguments, functions come in two flavors:

• No arguments: Many functions don’t require arguments. For example, the NOW() function returns the current date and time, and it doesn’t require arguments.

• One or more arguments: Most functions accept at least one argument, and some accept as many as nine or ten arguments. These arguments fall into two categories: required and optional. Required arguments are the arguments you must include when you use the function; otherwise, the formula will generate an error. You use the optional arguments only if your formula needs them.

Let’s look at an example. The FV() function determines the future value of a regular investment, based on three required arguments and two optional ones:

Click here to view code image

FV(rate, nper, pmt[, pv][, type])

This is called the function syntax. Three conventions are at work here and throughout the rest of this book:

• Italic type indicates a placeholder. That is, when you use the function, you replace the placeholder with an actual value.

• Arguments surrounded by square brackets are optional.

• All other arguments are required.

FV(rate, nper, pmt, pv)

FV(rate, nper, pmt, , type)

For each argument placeholder, you substitute an appropriate value. For example, in the FV() function, you substitute rate with a decimal value between 0 and 1, nper with an integer, and pmt with a dollar amount. Arguments can take any of the following forms:

• Literal alphanumeric values

• Expressions

• Cell or range references

• Range names

• Arrays

• The result of another function

The function operates by processing the inputs and then returning a result. For example, the FV() function returns the total value of the investment at the end of the term. Figure 4-1 shows a simple future-value calculator that uses this function. (In case you’re wondering, I entered the Payment value in cell B4 as negative because Excel always treats any money you have to pay as a negative number.)

• You can enter the function name in either uppercase or lowercase letters. Excel always converts function names to uppercase.

• Always enclose function arguments in parentheses.

• Always separate multiple arguments with commas. (You might want to add a space after each comma to make a function more readable. Excel ignores the extra spaces.)

• You can use a function as an argument for another function. This is called nesting functions. For example, the function AVERAGE(SUM(A1:A10), SUM(B1:B15)) sums two columns of numbers and returns the average of the two sums.

Excel offers the AutoComplete feature for function names that shows you a list of functions that begin with the characters you’ve typed in a formula. As you can see in Figure 4-2, when you begin typing a name in Excel, the program displays a list of the functions that start with the letters you’ve typed and displays a description of the currently selected function. Select the function you want to use and then select Tab to include it in the formula (or double-click the function).

After you select the function from the AutoComplete list (or when you enter a function name followed by the left parenthesis), Excel displays a pop-up banner that shows the function syntax. The current argument is displayed in bold type. In the example shown in Figure 4-3, the nper argument is shown in bold, so the next value (or cell reference, or whatever) entered will apply to that argument. When you enter a comma, Excel bolds the next argument in the list.

• You’re not sure which function to use.

• You want to see the syntax of a function before you use it.

• You want to examine similar functions in a particular category before you choose the function that best suits your needs.

• You want to see the effect that different argument values have on the function result.

For these situations, Excel offers two tools: the Insert Function feature and the Function Wizard.

You use the Insert Function feature to select the function you want from a dialog box. Here’s how it works:

1. Select the cell in which you want to use the function.

2. Enter the formula up to the point where you want to insert the function.

3. Choose one of the following:

   • If the function you want is one you inserted recently, it might appear on the list of recent functions in the Name box. Drop down the Name box list (see Figure 4-4); if you see the name of the function you want, select it and skip to step 7.

     

   • To pick any function, select Formulas > Insert Function. (You can also select the Insert Function button in the formula bar—see Figure 4-4—or select Shift+F3.) In this case, the Insert Function dialog box appears, as shown in Figure 4-4.

4. (Optional) In the Or Select A Category list in the Insert Function dialog box, select the type of function you need. If you’re not sure, select All.

5. In the Select A Function list, select the function you want to use. (Note that once you’re in the Select A Function list, selecting a letter moves the selection down to the first function that begins with that letter.)

6. Select OK. Excel displays the Function Arguments dialog box.

   Tip

   To skip the first six steps and go directly to the Function Arguments dialog box, enter the name of the function and the left parenthesis and then either select the Insert Function button or select Ctrl+A. Alternatively, select the equal sign (=) key and then select the function from the list of recent functions in the Name box. To skip the Function Arguments dialog box altogether, enter the name of the function in the cell and then select Ctrl+Shift+A.

7. For each required argument and each optional argument you want to use, enter a value, an expression, or a cell reference in the appropriate text box. Here are some notes to bear in mind when you’re working in this dialog box (see Figure 4-5):

   • The names of the required arguments are shown in bold type.

   • When you move the cursor to an argument text box, Excel displays a description of the argument.

   • After you fill in an argument text box, Excel shows the current value of the argument to the right of the box.

   • After you fill in the text boxes for all the required arguments, Excel displays the current value of the function.

   

8. When you’re finished, select OK. Excel pastes the function and its arguments into the cell.

In early versions of Excel (that is, prior to Excel 2007), the Analysis ToolPak included dozens of powerful functions. In Excel 2007 and later, however, all those functions are now part of the Excel function library, so you can use them without loading the Analysis ToolPak.

If you need to use the Analysis ToolPak features, you must load the add-in that makes them available to Excel. The following procedure takes you through the steps:

1. Select File > Options to open the Excel Options dialog box.

2. Select Add-Ins.

3. In the Manage list, select Excel Add-Ins and then select Go. Excel displays the Add-Ins dialog box.

4. Select the Analysis ToolPak check box, as shown in Figure 4-6.

   

5. Select OK.